This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain a Start & End Date which signifies the time span when a record is active. Because two records cannot be active at the same time, we can be sure that the Start & End dates will never overlap. While this describes one particular scenario for this example, there are many other situations this method can be used. Let’s look at the typical query we would use to find the record which is active for an employee at a specific point in time. [cc lang=”sql”] SELECT * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND ‘2001-04-28’ BETWEEN StartDate AND EndDate [/cc] This query will return to us the single record that is active as of ‘2001-04-28’. However if you think about the constraints our table contains, we actually only need to query one column (the StartDate) in order to isolate this record. This is because the StartDate will not overlap any other StartDate & EndDate for this employee. Here’s an example: [cc lang=”sql”] SELECT TOP 1 * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND StartDate >= ‘2001-04-28’ ORDER BY StartDate [/cc] Because only one record can be active for an employee at a single point in time, […]
Continue reading ...
Adding an identity column to a SELECT..INTO query is probably something I use nearly on a daily basis. I’m providing it here merely for reference purposes. It useful when you want to loop through a temporary table. [cc lang=”sql”] SELECT id = IDENTITY(int,1,1) ,FirstName = pc.FirstName ,LastName = pc.LastName INTO #person_contact FROM Person.Contact pc WHERE EmailPromotion = 2 ORDER BY pc.LastName [/cc] The first parameter for the IDENTITY function is that data type. It must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. The second parameter is the seed (or starting) value. The third parameter is the increment. An alternative method is to use the ROW_NUMBER window function. This can actually be a bit more pliable in certain situations because of it’s optional partitioning function. The obvious difference here though is that any rows subsequently added to the table do not get an identity value. [cc lang=”sql”] SELECT id = ROW_NUMBER() OVER (ORDER BY pc.LastName) ,FirstName = pc.FirstName ,LastName = pc.LastName INTO #person_contact2 FROM Person.Contact pc WHERE EmailPromotion = 2 [/cc] Note that with the IDENTITY method, Microsoft does not guarantee that the identity will be in the correct order when performing an order by clause, especially when using TOP or ROWCOUNT.
Continue reading ...
If you’ve ever wondered how to make a procedure available in any database, it’s actually pretty simple. If you create a procedure in the master database with the prefix of “sp_”, it will be callable from any database. I personally think this feature is great for utilities, however I would not use this for dependent objects. In other words, I would not advise creating a procedure (or function that is referenced by other procedures) within the master database. The biggest reason is that when restoring databases to other servers, you typically do not restore the master database. So it can be easily left out or forgotten. If that happens you may have a mission critical troubleshooting situation on your hands. However sometimes there may not be a way around it. But you can look into utilizing synonyms as an alternative. Another interesting observation regarding placing procedures in the master database with the sp_ prefix is that the procedure is actually executed from the context of the master database. In other words, if you create a procedure to enumerate all the other procedures within your database and place it in master, once you execute the procedure from a database other than master, it will still enumerate the procedures in the master database. Here’s an example: After creating this procedure and calling it from AdventureWorks, it still enumerates all the procedures within the master database. To get around this, we can use the undocumented procedure which will mark the procedure as a […]
Continue reading ...
These simple snippets of code will show all triggers in the current database, along with the tables they belong to. Unfortunately the INFORMATION_SCHEMA views do not show triggers so we need to revert to using less documented options. The proper way would be to use the INFORMATION_SCHEMA.TRIGGERS view, however SQL Server has yet to create and publish this view. In the meantime we need to use one of the following workarounds: [cc lang=”sql”] SELECT table_name = so.name ,trigger_name = st.name ,trigger_text = sc.text ,create_date = st.create_date FROM sys.triggers st JOIN sysobjects so ON st.parent_id = so.id JOIN syscomments sc ON sc.id = st.[object_id] [/cc] You can also use only sysobjects: [cc lang=”sql”] SELECT table_name = so2.name ,trigger_name = so.name ,trigger_text = sc.text ,create_date = so.crdate FROM sysobjects so JOIN sysobjects so2 ON so.parent_obj = so2.id JOIN syscomments sc ON sc.id = so.id WHERE so.type = ‘tr’ [/cc] I would imagine that SQL Server will provide more documented ways to query this information, until then we need to take a chance by deploying these solutions.
Continue reading ...
When optimizing procedures often times you need to rewrite the procedure in order to speed it up. However, when you’re finished how can you be sure that the result set is still the same? This article will show you how to run a thorough test to make sure. We’ll execute the old and new stored procedure then compare their result sets by using a checksum aggregate. We’ll also compare rowcounts and elapsed execution time. The benefit of this test harness is that it will allow you to loop through and execute the procedures with as many dynamic parameter values as you wish, then compare the result set of the old procedure vs the new procedure for each execution. First, we need to do a couple things to set up our environment. Setup a local linked server Create a wrapper procedure We need the local linked server in order to dynamically retrieve the result set of the stored procedure using OPENQUERY. We could skip this step and create the temp table explicitly if we knew all the columns and data types being returned, but sometimes this is a hassle because there could be many columns that are returned and explicitly creating a temp table for each procedure we wanted to test is a pain. And that’s probably why you’re here in the first place isn’t it? So to create a local linked server, do the following: [cc lang=”sql”] EXEC sp_addlinkedserver @server=’LOCALSERVER’, @srvproduct=’SQLSERVER’, @provider=’SQLNCLI’, @datasrc=’Your_Server_Name_Here’ [/cc] For this example, the server name […]
Continue reading ...
