Generating a date table on the fly is nice to have for many reasons. The most recent of my adventures required determining when employees did not enter their time in their timesheets. I know I had a tattle tale job, the ironic part is that I ended up getting in trouble the next day for [...]
Archive of posts filed under the SQL category.
SQL Server Add Primary Key
Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non clustered indexes, please [...]
SQL Server Select
The SQL Server Select statement is the first statement used when returning data. It is the most used and most important statement in the T-SQL language. The Select statement has many different clauses. We will step through each clause further in the tutorial, however now, we will look at Select itself. The following SELECT ‘Mike’ [...]
How to Compare Stored Procedure Result Sets
Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server method. Your method [...]
Get End of Month
The following short code will return the date and time for the very end of the month passed in. It works by first getting the beginning of the month for the date passed in, adding one month to that date, then subtracting 3 milliseconds. This gives the latest possible date for the end of the [...]
Beginning of Month
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: DECLARE @Date datetime SET @Date = GETDATE() SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) Popular search terms:sql server beginning of the monthsql beginning of monthSQL server Beginning of Monthbeginning monthsql server start of [...]
Create Index on Table Variable
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. DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), UNIQUE (UserName) [...]
Create Index on Temp Table
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 [...]
Random Number
The following SQL script will generate a single random number: SELECT CAST(1000000-CEILING(RAND()* 899001) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) as int) Popular search terms:ssrs random numbersql server secure random number1000000 random numbersql server 2008 random numbersql reporting generate randon number
Conditional Where Clause
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 [...]
