T/SQL

SQL Server gets an ANSI compliant unique index … sort of

If you work with any of the other big database platforms you’ve probably noticed that SQL Server’s implementation of a unique index is “different” than the others.  Until now there hasn’t been a way to fix that without using a trigger.  Until now… Continued »

Understanding what the WITH ROLLBACK IMMEDIATE does

Based on some questions on forums and some of the responses that people have been giving there appears to be done misunderstanding about what the WITH ROLLBACK IMMEDIATE option does when added to the ALTER DATABASE command.  My goal here is to try and clear up at least some of the confusion. Continued »

Dates can easily be the hardest datatype to work with.

The datetime data type can be one of the hardest to work with when it comes to index optimization.  Most queries that use a datetime data type for filtering (part of the where clause) only want to match the date portion of the value.  Most people handle this via a convert function around the date column.  This causes the index that you create to become useless as the convert function causes the index to be scanned not seeked.

Continued »

Difference between an Index and a Primary Key

There seams to be some confusion between what a Primary Key is, and what an Index is and how they are used.

The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null. Because they are unique and not null, these values (or value if your primary key is a single column) can then be used to identify a single row in the table every time. In most if not all database platforms the Primary Key will have an index created on it.

An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are part of the index. When you create an index within the database, you are creating a physical object which is being saved to disk. Using a table which holds employees as an example:

CREATE TABLE dbo.Employee
 (EmployeeId INT PRIMARY KEY,
 LastName VARCHAR(50),
 FirstName VARCHAR(50),
 DepartmentId INT,
 StartDate DATETIME,
 TermDate DATETIME,
 TermReason INT)

The EmployeeId is the Primary Key for our table as that is what we will use to uniquely identify an employee. If we were to search the table based on the last name the database would need to read the entire table from the disk into memory so that we can find the few employees that have the correct last name. Now if we create an index on the LastName column when we run the same query, the database only needs to load the index from the disk into memory, which will be much quicker, and instead of scanning through the entire table looking for matches, because the values in the index are already sorted the database engine can go to the correct location within the index and find the matching records very quickly.

Hopefully this will help sort out some of the confusion.

Denny

Damn rollback scripts are a pain

Writing release scripts for a database is easy.  What ever new objects you want you create you create, and what ever objects you need to change you change.  Piece of cake.  Rollback scripts however are a bit trickier since you need to know what everything should look like after the script is done. Continued »

Changing the default owner when creating objects

When a user that doesn’t have sysadmin rights creates objects by default they will be created in the schema that is the users default schema.  Now the catch to this is that if you grant the user rights into the database via a domain group that domain group then the user doesn’t have a default schema.

So, now how do you fix this?  Unfortunately the only fix to this is to grant the users Windows login as a separate login, then grant this login rights into the database.  You can then grant the user which is mapped directly to the users Windows login a default schema of dbo.

Because of this the user should specify the schema when creating objects.

The downside to this is that they won’t be able to use the object editor to create new tables.  All new tables will need to be created in T/SQL directly.

Denny

Non-sysadmins create tables under own schema

When using SQL Server 2000 and the user doesn’t have sysadmin rights, and their login isn’t mapped to the dbo user within the database all objects created will be, by default created under the user schema.

This is the normal behavior of SQL Server 2000.  In order to allow users who are not members of the sysadmin fixed server role to create objects under the dbo schema by default you have to map their login to the dbo user, even if they are a member of the dbo fixed database role.

To work around this, in the T/SQL code specify the owner of the database object.  If your developer is using Enterprise Manager to create the new tables before saving the table, click on the properties button in the upper left hand corner of the Enterprise Manager window (second from the left).  Then change the owner drop down from their username to dbo.  There is no way to default this setting to dbo so it will need to be changed for each new table being created.

Denny

How can I remove duplicate records in my tables?

All to often we end up with duplicate rows in a table.  The best way to keep duplicate rows out of the database is to not let them in.  But assume that they are there.  This bit of sample code shows how to delete those duplicate rows quickly and easily in a single statement.  No temp tables required (I use a temp table to put the data into for example purposes).  This code is for SQL 2005 and up as it uses some features which were introduced in SQL Server 2005.  SQL Server 2000 would require a totally different technique.

CREATE TABLE #DuplicateRows /*Create a new table*/
(Col1 INT,
Col2 INT,
Col3 INT)

INSERT INTO #DuplicateRows /*Load up duplicate rows*/
SELECT 1,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT 2,2,2
UNION ALL
SELECT 2,2,2
UNION ALL
SELECT 2,2,2

SELECT *
FROM #DuplicateRows; /*Check that the data is actually hosed*/

WITH Cleaning AS (SELECT ROW_NUMBER() OVER(ORDER BY Col1, Col2, Col3) as row,
Col1,
Col2,
Col3
FROM #DuplicateRows)

DELETE FROM Cleaning /*Delete the rows which are duplicates*/
WHERE Row NOT IN (SELECT row FROM  (SELECT Col1, Col2, Col3, MIN(row) row
FROM Cleaning a
GROUP BY Col1, Col2, Col3) b)

SELECT * /*Check the table to see that it is clean*/
FROM #DuplicateRows

DROP TABLE #DuplicateRows /*Clean up the table*/

Hopefully you find this code useful.

Denny

Viewing the domain groups that are used to get into SQL

Last week Linchi Shea wrote a posted a blog entry entitled “How does that AD user account get access to the database?“.  In it Linchi shows a method of finding out the domain groups that a SQL Server user uses to access the database.

There is however an easier, method you can use.

There is a system extended stored procedure called xp_logininfo.  Microsoft was even kind enough to document the procedure for us.  You can use this procedure to see what group a use belongs to, or what users are in a domain group, all from T/SQL.

For example, on my sandbox instance if I run

I get a result set which says that I gained my access to the SQL Server via the “BUILTIN\Administrators” group.

This is a nice quick and easy method to see what domain group a user used to access the SQL Server.

Denny

Back To Basics: Getting Data from an XML Document

One of the most popular ways to get data multiple pieces of data in a single parameter from one stored procedure to another, or from a client application to the database is to use XML. This can be done in SQL Server 2000 by using the NTEXT (or TEXT) datatype, and in SQL 2005 using the XML datatype. (In SQL Server 2008 you can use Table variable input parameters.)

Continued »