How To Create A Date Table


Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data on those days. SQL Server doesn’t have a function to generate dates in a range. So to accomplish this, I create a date table using the following script.


What Is SQL Server


What Is SQL Server
SQL Server is a database management system (DBMS) produced by Microsoft. The purpose of SQL Server is to store and retrieve data for use in computer applications. These applications could be mobile apps, web sites, desktop applications, or more.

Data in SQL Server is stored in tables. These tables have rows and columns, much like you would see in an Excel spreadsheet. SQL Server accesses this data using a coding language called T-SQL (Transact-SQL). This code is called a query. Here is a sample query that pulls all of the data from a table.

Easiest Definition Of “What Is SQL Server”
SQL Server is a program built by Microsoft that other programs can use to store and retrieve data.

List Of Data Types And Their Sizes


The below table shows the storage sizes of the different data types in SQL Server.

DateTime Data Types
date 3 bytes
datetime 8 bytes
datetime2 6 – 8 bytes (depending on precision)
datetimeoffset 10 bytes
smalldatetime 4 bytes
time 5 bytes


Numeric Data Types
decimal 5 – 17 bytes
numeric 5 – 17 bytes
float 4 or 8 bytes
real 4 bytes
bigint 8 bytes
int 4 bytes
smallint 2 bytes
tinyint 1 byte
money 8 bytes
smallmoney 4 bytes


char size defined in table
varchar 2 bytes + data size
text data size
nchar 2 times size defined in table
nvarchar 2 bytes + 2 times data size
ntext 2 times data size


binary size defined in table
varbinary 2 bytes + data size
image 2 bytes + data size


bit 2 bytes
hierarchyid 5 bytes
uniqueidentifier 16 bytes


SQL Server 2014 Feature – Timeout For Online Index Rebuilding


SQL Server 2014 comes with a new argument for how to handle the blocking issues that come from online index rebuilds.

Before SQL Server 2014

Before the existence of SQL Server 2014, if you were doing an online index rebuild… SQL Server would wait until the index rebuild could get the locks that it needs for rebuilding the index.  This can cause long running maintenance plans on very active tables.

SQL Server 2014 Online Index Rebuild Argument

The new functionality available in SQL Server 2014 tags on to the original index/table rebuild syntax.  It allows you to customize how you want to handle the blocking that occurs on the indexes/tables that are being rebuilt.  Here is a sample of the new syntax for managing the blocking on the index rebuild.

Notice in the statement above, the new WAIT_AT_LOW_PRIORITY flag that gets added to the ONLINE argument.  When you rebuild an index in SQL Server with the WAIT_AT_LOW_PRIORITY flag turned on, it will allow other operations to proceed while the index build waits for low priority locks.  This argument comes with 2 parameters.


The MAX_DURATION parameter is how long you want to wait in minutes.  It must always be in minutes.


This parameter comes with 3 options.  This is basically who loses if the rebuild process cannot get the locks that it needs.  The connection that you choose will be terminated after the timeout period.

  • NONE – Continue waiting for the lock
  • SELF – The rebuild operation will fail
  • BLOCKERS – All transactions that are blocking the rebuild will be killed

If you do not specify WAIT_AT_LOW_PRIORITY with you index rebuild, then SQL Server will automatically set it to the default of 0 minutes and NONE.

Index Reference:

Table Reference: