SQL Server with Mr. Denny:

T/SQL


February 8, 2010  11:00 AM

Using XMLNAMESPACES within a function



Posted by: Denny Cherry
Functions, T/SQL, XML

Recently one of my developers asked me to begin returning data from a function as XML instead of a comma separated list.  My thoughts on this was awesome, another cursor getting removed from the system.  I'll write a quick FOR XML AUTO, ROOT('root') at the bottom of the SELECT statement being...

October 15, 2009  11:00 AM

SQL Server gets an ANSI compliant unique index … sort of



Posted by: Denny Cherry
Index, SQL, T/SQL, Unique Index

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...


September 24, 2009  11:00 AM

Understanding what the WITH ROLLBACK IMMEDIATE does



Posted by: Denny Cherry
T/SQL

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.


September 17, 2009  11:00 AM

Dates can easily be the hardest datatype to work with.



Posted by: Denny Cherry
Index Performance, Index Scan, Index Seek, Indexing, T/SQL

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...


August 31, 2009  6:26 PM

Difference between an Index and a Primary Key



Posted by: Denny Cherry
T/SQL

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...


August 26, 2009  12:50 PM

Damn rollback scripts are a pain



Posted by: Denny Cherry
sys.tables, T/SQL

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...


July 20, 2009  11:00 AM

Changing the default owner when creating objects



Posted by: Denny Cherry
Permissions, SQL Server 2005, SQL Server 2008, T/SQL, Tables

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...


July 16, 2009  11:00 AM

Non-sysadmins create tables under own schema



Posted by: Denny Cherry
Enterprise Manager, SQL Server 2000, T/SQL, Tables

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...


May 4, 2009  11:00 AM

How can I remove duplicate records in my tables?



Posted by: Denny Cherry
CTE, DELETE statement, SQL Server 2005, SQL Server 2008, T/SQL

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...


March 29, 2009  12:05 AM

Viewing the domain groups that are used to get into SQL



Posted by: Denny Cherry
Extended Stored Procedure, Linchi Shea, T/SQL, xp_logininfo

Last week Linchi Shea wrote a posted a blog entry entitled "How does that AD user...

Bookmark and Share     0 Comments     RSS Feed     Email a friend


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: