Hi, I want to understand the process of nested join clauses in sql queries. Can you explain this example with pseudo codes? (What is the order of joining tables?) FROM table1 AS t1 (nolock) INNER JOIN table2 AS t2 (nolock) INNER JOIN table3 as t3 (nolock) ON t2.id = t3.id ON t1.mainId = t2.mainId ...
As part of our DB change control process one of the tasks is to reverse engineer the changes into our schema diagram, using Visio. This is a little tedious and we were looking at a way to automate this. We already have a CI and Release to test process that generates the latest DB all we need is some application that will analyse the da...
Just a question about best-practices when upgrading an existing database. Assuming there will be all kinds of modifications to the data itself, the structure, the relations, additional columns, disappearing columns and whatever more. My problem is a simple one. I'm working on a project that will use SQL Server. No problem there, since I...
Hi, I have three tables and I want to update all values for a particular type to the same value: table1: id, ValueType table2: id, Value table3: id, fkValueTypeId, fkValueId fkValueType references ID in table1. fkValue references ID in Table2 I am trying to set all Speed values to the same value: i.e. Table1: 0, speed 1, age 2, c...
I have few questions on SQL.. How to analyze the performance of a query? Any software, inbuilt features of MSSQL server 2005/2008? What should be used in place of inin queries so that the performance is better? Eg: SELECT * FROM enquiry_courses WHERE enquiry_id IN ( SELECT enquiry_id FROM enquiries WHERE session_id = '4cd3420a1...
Is there a better way to write the following simple SQL Server 2005 update statement? This just seems a bit messy inefficient. UPDATE QuotationItem SET Recurring_Cost = (SELECT TOP (1) Recurring_Cost FROM Products WHERE (Remote_ID = QuotationItem.Product_ID)) WHERE (Quotation_ID ...
I have a column with bigint datatype in SQL Server 2005. I want to store 0347 in that.. (0 should not be removed) means their must be at least four value like: 0034 , 0007, 0423,4445. ...
I have to work with multiple SQL Server tables that generally look like this: int id_this, int id_that, ..., double Value1, double Value2, ..., double Value96 I know this sucks, but I can't change it. What I want to do now is to define some class like public class Foo { public int Id_This { get; set; } public int Id_That { ge...
I'm having a problem with an insert query in SQL Server. The full text of the query is insert into franchise (fran_id, name, address1, address2, city, state, zip, email, phone, text) values(0, "DevFranchise1", "101 Main St.", "-", "Brighton", "Mi", "48116", "[email protected]", 8105551234, "asdflkjsadf"); Now "state" and "text...
I am trying to drop one of the role in my SQL Server database. I dropped all the members from the role and when i tried to drop role i got this error message: Msg 15138, Level 16, State 1, Line 13 The database principal owns a schema in the database, and cannot be dropped. Does anyone know why? I checked the Owned Schema and it only h...
I am designing a database from scratch from manufacturing products. The key design is as follows: (Currently I am working on MS Access 2007 but my final implementation is SQL Server i.e. SQL Server 2008 being the latest version. Also I would be using only Express edition at this movement. The application will involve a .NET storefront in...
In order to improve the performance of a query I have created a denormalized indexed view that contains some of the information I need to report on. When I didn't get the performance gains that I had hoped for I created a table version of my view with indexes and got significantly better performance. I should note that when I create ...
How can we show all the securable that is added in any particular role in script? ...
Hello, While investigating a problem I believed to be related to lock escalation and thinking that READ_COMMITTED_SNAPSHOT would be the solution, I checked whether it was already enabled. To my surprise, the database had compatibility level 80 (SQL Server 2000, and I'm new here, and nobody or document seems to know why!). But READ_COM...
I have an insert statement that pulls some data into a few table variables and then based on that data does a few inserts into several tables. I only care about the rows that are inserted into the real tables and not the table variables, but ExecuteNonQuery will return the sum of all @@ROWCOUNT's. What I would like to know is there a way...
I have use Identity on ID primary key. And then I insert some data. For example. Data 1 -> Add Successful without error. ID 1 Data 2 -> Add Successful without error. ID 2 Data 3 -> Add Fail with error. Data 4 -> Add Fail with error. Data 5 -> Add Successful without error. ID 5 You can see that ID has jump from 2 t...
I created a login with Windows Authentication. The properties window for it has Server Authentication section greyed out. Can you switch the user to SQL Server Authentication? The server is set for SQL Server and Windows Authentication mode. ...
In Microsoft SQL Server, I want to alter a field to "NOT NULL" AND DEFAULT ''. I've already issued the following two commands: ALTER TABLE USR ADD Country Varchar(128) UPDATE USR SET Country='' Now I need 1. ALTER TABLE USR ADD CONSTRAINT CountryIsNotNull something 2. ALTER TABLE USR ADD CONSTRAINT CountryDefault default '' ...
How can I drop user from a database without dropping it's logging? The script should check if the user exists in database, if does then drop the user. ...
I'm looking for a little advice on how to setup a database to hold numeric data for a modeling application. My users have a spreadsheet that holds data for use in a modeling application. The data is structured as such: Each tab is a line of business, columns are years and rows are elements. The cells are standard decimal numbers xx.xx...