SQL Server Scripts

T-SQL EXCEPT and INTERSECT

Except and Intersect Venn Diagram

T-SQL EXCEPT and INTERSECT

Both T-SQL EXCEPT and INTERSECT are set based operators that combine multiple query results back in the same result set.  EXCEPT returns the records from the query on the left that are not found in the right query.  INTERSECT returns the distinct rows that are in both the...Read More »


T-SQL UNION Set Operator

T-SQL UNION

Set operators allow you to combine data from two or more queries and return the results in a single set.  There are 3 set operators available T-SQL
EXCEPT, INTERSECT and UNION.
This article is focused on the UNION operator.  UNION is used when you need to combine data from multiple queries...Read More »

T-SQL Random Numbers using RAND() for Test Data

T-SQL Random Numbers using RAND()

SQL Server includes the T-SQL RAND() function to create a random value between 0 and 1 of float datatype.  To create a random number execute Select Rand(), in my example it returned .0131039082850364.  If I wanted to always return the same number I can include a...Read More »


ROW_NUMBER SQL Server

row_number Order By

Row_Number SQL Server

SQL Server includes several Ranking Functions that can be called in T-SQL.  One of these is the Row_Number() function.  You can use this function to return a sequential number in your result set that begins at 1.

There are 2 arguments that can be passed into the function that...Read More »


SQL Server Table Partition Example

Table Partition SQL Server

Why Use a SQL Server Table Partition

Data warehouses and data repositories often have tables that store millions of records and each day adding thousands more.  As the data ages, the older records generally require fewer updates.  If all of these records are kept in a single physical table.  Queries and...Read More »

SQL Server TABLESAMPLE

TABLESAMPLE

Using TABLESAMPLE in SQL Server

SQL Server has several ways for you to limit the number of records returned from a query.  For example, the AdventureWorks [sales].[salesorderdetail] table has 121,317 rows loaded in sequential order starting with SalesOrderDetailID 1.  By Using a top 1000 clause I can limit the results to...Read More »


SQL Server Checksum

SQL Server Checksum

Use SQL Server Checksum in Data Warehousing

When building data warehouses you...Read More »


Common Table Expressions (CTE)

Common Table Expresseion (CTE)

A common table expression (CTE) is a derived table that is defined and only stored for the duration of the query. CTE’s can be used for recursive queries, creating a view on demand and referencing the same table multiple times in the same query.  CTE’s make your...Read More »