Possibly the easiest way to calculate the beginning of the month, is to use the DateAdd function and add a date difference of nothing to the month: [cc lang=”sql”] DECLARE @Date datetime SET @Date = GETDATE() SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) [/cc]
Continue reading ...Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index. [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), UNIQUE (UserName) ) [/cc] The drawback is that the indexes (or constraints) need to be unique. One potential way to circumvent this however, is to create a composite unique constraint: [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), FirstName varchar(50), UNIQUE (UserName,UserID) ) [/cc] You can also create the equivalent of a clustered index. To do so, just add the clustered reserved word. [cc lang=”sql”] DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), FirstName varchar(50), UNIQUE CLUSTERED (UserName,UserID) ) [/cc] Generally, temp tables perform better in situations where an index is needed. The downfall to temp tables is that they will frequently cause recompilation. This was more of an issue with SQL 2000 when compilation was performed at the procedure level instead of the statement level. SQL 2005 and above perform compilation at the statement level so if only one statement utilizes a temp table then that statement is the only one that gets recompiled. Contrary to popular belief, table variables can and do write to disk.
Continue reading ...One of the most valuable assets of a temp table (#temp) is the ability to add either a clustered or non clustered index. Additionally, #temp tables allow for the auto-generated statistics to be created against them. This can help the optimizer when determining cardinality. Below is an example of creating both a clustered and non-clustered index on a temp table. [cc lang=”sql”] CREATE TABLE #Users ( ID int IDENTITY(1,1), UserID int, UserName varchar(50) ) INSERT INTO #Users ( UserID, UserName ) SELECT UserID = u.UserID ,UserName = u.UserName FROM dbo.Users u CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Users(UserID) CREATE INDEX IDX_Users_UserName ON #Users(UserName) [/cc] Even though you can implicitly create a clustered index on a table variable (@table) by defining a primary key or unique constraint, it is generally more efficient to use a temp table. The downside is recompilation, however this can be suppressed with the use of (Keep Plan) or (Keep Fixed Plan) hints.
Continue reading ...The following SQL script will generate a single random number: [cc lang=”sql”] SELECT CAST(1000000-CEILING(RAND()* 899001) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) as int) [/cc]
Continue reading ...A very powerful yet obscure feature in SQL is the ability to have parts of the where clause conditionally execute. Most often developers will make duplicate copies of an entire query to facilitate two different variable choices. Below is an example stored procedure to return employees that has an optional parameter to return employees based on GenderType. If no values is passed into the parameter, all employees will be returned. If the parameter is filled it will return the employees of that gendertype. Many developers will write the query this way: [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN IF @GenderType IS NOT NULL BEGIN SELECT * FROM dbo.Employees e WHERE e.GenderType = @GenderType END ELSE BEGIN SELECT * FROM dbo.Employees e END END [/cc] As you can see, the same query is repeated twice. This accounts for twice the maintenance and two different execution plans being generated (depending on which version of SQL Server you are using). Now we’ll write the same query with a conditional where clause [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN SELECT * FROM dbo.Employees e WHERE (@GenderType IS NULL OR e.GenderType = @GenderType) END [/cc] What we did here is utilize the power of the OR statement in the Where clause. In an or statement if the left side of the OR statement evaluates to true, then the second side is not evaluated. So when no value is entered for @GenderType, then the […]
Continue reading ...
