Tag Archives: T-SQL

How to assess T-SQL code quickly

I’m sure you’re an excellent SQL coder writing beautiful efficient queries, but your predecessor . . . well they might have just been lucky to have a job.

Going through someone else’s bad code is usually tiresome, tedious and often very confusing.

I’ve created the T-SQL Assessor excel file to help in this task.

DOWNLOAD (Dropbox link)

The assessor will colour code the sql to highlight the lines of importance. With the Key Word column you can then simply filter to words like INSERT, UPDATE, MERGE and EXEC to see where the data is going or filter the column by the word FROM to see where the data has come from.

To use the T-SQL Assessor file you will first have to format your code using Poor Man’s T-SQL Formatter. This excellent tool can be installed in Visual Studio, SQL Server Management Studio or Notepad++.

http://architectshack.com/PoorMansTSqlFormatter.ashx

You can also use the online option:

http://poorsql.com/

Poor Man’s T-SQL Formatter makes text that contains a SQL command a new line, so you can’t have INSERT and FROM on one line. This is what allows Excel formula’s to highlight the lines with key words as each line can only contain one key word, excluding comments.

Once the code is formatted simply paste it into the first sheet of the file, “SQL”.

That’s it, all the work is then done for you on the second sheet of the file, “SQL Assessed”

T-SQL Assessor is also great at preparing a report from a schema compare script created by Visual Studio. It’s very annoying Microsoft didn’t provide a way of exporting the comparison directly into excel the way Redgate did but this will help. Simply filter the file to only include the keywords.

DOWNLOAD (Dropbox link)

How to archive tables that contain a keyword from one database to another using T-Sql

In the previous article I wrote about how to identify and remove unwanted tables. Link

In that tutorial I suggested prefixing the tables you want to remove with _DELETE_.

I also suggested you may want to archive the tables in some manner before you delete them. This could be because there is a chance someone might come looking for the data that was in a deleted table or something might break by removing the table and you might want to put it back asap.

If you have only a few tables you could script the tables out but if you have a lot of tables that becomes a little unmanageable. Also if the tables total in size to 10 Gb the script to recreate the tables will be a lot lot larger.

An alternative method is to create an Archive database, copy the tables across to this target database and then delete the tables in the source database. You can then backup and drop the Archive database saving the .bak file somewhere cheaper.

The script below will allow you to do just that. It prints the T-Sql to do the job, it doesn’t carry out the job, so it’s completely safe to execute and review.

To use the script below create a target database.

Use the target database name for the variable value @TargetDb

Use the source database name for the variable value @SourceDb

The @KeyWord variable is used to gather all the tables that contain the string of choice, in the example below _DELETE_.

The @RemoveKeyWord variable is a flag that will remove the keyword string from the target database table name, e.g. _DELETE_Sales will become Sales.

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
	DROP TABLE #Table
GO

DECLARE @RemoveKeyWord BIT
DECLARE @Id AS INT
DECLARE @KeyWord AS VARCHAR(256)
DECLARE @SourceDb AS SYSNAME
DECLARE @TargetDb AS SYSNAME
DECLARE @TableName AS SYSNAME
DECLARE @SchemaName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @RemoveKeyWord = 1
SET @KeyWord = '_DELETE_'
SET @TargetDb = 'Archive'
SET @SourceDb = 'Source'
SET NOCOUNT ON

CREATE TABLE #Table (
	Id_Table INT IDENTITY(1, 1)
	,SchemaName SYSNAME
	,TableName SYSNAME
	);

SET @Sql = '
INSERT INTO #Table (
	SchemaName
	,TableName
	)
SELECT s.NAME
	,so.NAME
FROM ' + QUOTENAME(@SourceDb) + '.sys.tables AS so
LEFT JOIN ' + QUOTENAME(@SourceDb) + '.sys.schemas AS s ON so.schema_id = s.schema_id
WHERE so.NAME LIKE ' + '''' + '%' + @KeyWord + '%' + '''' + '
ORDER BY s.NAME ASC'

EXEC (@Sql)

SET @SchemaName = ''

WHILE @SchemaName IS NOT NULL
BEGIN
	SET @SchemaName = (
			SELECT MIN(SchemaName)
			FROM #Table
			WHERE SchemaName > @SchemaName
				AND SchemaName <> 'dbo'
			)

	PRINT 'USE ' + QUOTENAME(@TargetDb) + ';
GO
	
IF NOT EXISTS (SELECT * FROM ' + QUOTENAME(@TargetDb) + '.sys.schemas WHERE name = ' + '''' + @SchemaName + '''' + ')
  BEGIN
    EXEC (' + '''' + 'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + ';' + '''' + ');
  END;
  
'
END

SET @Id = 1

WHILE @Id IS NOT NULL
BEGIN
	SELECT @TableName = TableName
		,@SchemaName = SchemaName
	FROM #Table
	WHERE Id_Table = @Id

	IF @RemoveKeyWord = 1
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(REPLACE(@TableName, @KeyWord, '')) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END
	ELSE
	BEGIN
		PRINT '
SELECT *
INTO ' + QUOTENAME(@TargetDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' 
FROM ' + QUOTENAME(@SourceDb) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
	END

	SET @Id = (
			SELECT MIN(Id_Table)
			FROM #Table
			WHERE Id_Table > @Id
			)
END

How to delete all SQL Server Jobs that have no maintenance plan with T-Sql

I’d really recommend not running the output of this script on a live environment! This is just a little something I whipped up to clear out a development environment.

Running the script will output the T-Sql required to deleted all jobs on the SQL Server instance. Jobs for maintenance plans are not included as maintenance plans need to be deleted first. Just copy the text and paste into a new SSMS window and execute. You could change the PRINT to EXEC but I wouldn’t recommend it. 

SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.Name
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysmaintplan_subplans AS p ON j.job_id = p.job_id
WHERE p.subplan_id IS NULL
ORDER BY j.Name ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_delete_job @job_name = ' + '''' + @JobName + '''' + '; 
'
	PRINT @Sql
END

How to turn a month name and year into a date field in SQL

When would I use this?

Say you have an SSRS report which provides users with the parameter options month and year and the report returns data for that month and year. You will likely need that month name (varchar) and year (int) to be converted into a date fields representing the first and last day of that month/year. Once you have those two dates they can then be used in the WHERE clause of your SQL query to return data within that range. The logic below will allow the conversion of month name and year into start and end dates described above. You can now take this logic and input it into a stored procedure or user defined function.

There will only ever be 12 months going forward but to create an ever updating parameter option for years please see this tutorial.

DECLARE @year AS INT
DECLARE @month AS VARCHAR(9)
DECLARE @monthNumber AS CHAR(2)
DECLARE @startDate AS DATE
DECLARE @endDate AS DATE;

SET @year = 2016
SET @month = 'February'

IF @year IS NOT NULL
BEGIN
	WITH monthPicker
	AS (
		SELECT CASE 
				WHEN @month = 'January'
					THEN '01'
				WHEN @month = 'February'
					THEN '02'
				WHEN @month = 'March'
					THEN '03'
				WHEN @month = 'April'
					THEN '04'
				WHEN @month = 'May'
					THEN '05'
				WHEN @month = 'June'
					THEN '06'
				WHEN @month = 'July'
					THEN '07'
				WHEN @month = 'August'
					THEN '08'
				WHEN @month = 'September'
					THEN '09'
				WHEN @month = 'October'
					THEN '10'
				WHEN @month = 'November'
					THEN '11'
				WHEN @month = 'December'
					THEN '12'
				ELSE NULL
				END AS monthPicked
		)
	SELECT @monthNumber = (
			SELECT monthPicked
			FROM monthPicker
			)

	SET @startDate = (
			SELECT CAST(CAST(@year AS VARCHAR(4)) + @monthNumber + '01' AS DATETIME)
			)
	SET @endDate = (
			SELECT DATEADD(s, - 1, DATEADD(MM, DATEDIFF(M, 0, @startDate) + 1, 0))
			)
END
ELSE
BEGIN
	SET @startDate = (
			SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
			)
	SET @endDate = (DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), - 1))
END

PRINT @startDate
PRINT @endDate

How to pass table valued parameters in SQL Server

With the introduction of SQL Server 2008 came the ability to define an entire table as a parameter, think of it like a table data type. This feature greatly eases the development process as constructing and parsing XML data strings is no longer necessary.

Table parameters are user defined parameters, i.e. you are creating a means of storing specific data that is passed by a stored procedure or function.

Limitations:

  • The READONLY clause must be used when passing in the table valued variable
  • Data in the table variable cannot be modified
  • The table variables cannot be used as OUTPUT parameters only input parameters.
  • When data is passed to the table variable the table variable must be passed to the stored procedure in the same batch. Table variables go out of scope as soon as the procedure or batch returns.

The following is a complete end to end example of how to create and pass data to table parameters:

IF OBJECT_ID('OrderHistory') > 0
	DROP TABLE OrderHistory;
GO

CREATE TABLE [dbo].[OrderHistory] (
	[OrderID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY
	,[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE TYPE OrderHistoryTableType AS TABLE (
	[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE PROCEDURE usp_InsertOrder (@TableVariable OrderHistoryTableType READONLY)
AS
BEGIN
	INSERT INTO OrderHistory (
		Product
		,OrderDate
		,SalePrice
		)
	SELECT Product
		,OrderDate
		,SalePrice
	FROM @TableVariable
END
GO

DECLARE @DataTable AS OrderHistoryTableType

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Desktop'
	,GETUTCDATE()
	,599.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Laptop'
	,GETUTCDATE()
	,299.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Mouse'
	,GETUTCDATE()
	,9.00
	)

EXEC usp_InsertOrder @TableVariable = @DataTable

SELECT *
FROM OrderHistory

 

If you want to view other types of table type definitions in your system, or you’ve forgotten what you called a specific table parameter, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

How to create a running total in SQL

Below is a simple example of how to create a running total in T-SQL using a self-join.

The total is created by summing the preceding values, the precedence of which is determined by, in this instance, the sale date field.

A date field, or another unique order field, is required for this technique to work otherwise all the values would be summed at once, based on grouping, and the summed value would be displayed for each relevant record.

CREATE TABLE #Product (
Product_ID INT
,Product VARCHAR(10)
)

CREATE TABLE #Sale (
    Product_ID INT
    ,SaleAmount MONEY
    ,SaleDate DATETIME
    )

INSERT INTO #Product
VALUES (
    1
    ,'Bike'
    )
    ,(
    2
    ,'Car'
    )
    ,(
    3
    ,'Truck'
    )


INSERT INTO #Sale
VALUES (
    1
    ,10
    ,'20150101 12:00:00.000'
    )
    ,(
    1
    ,10
    ,'20150102 13:00:00.000'
    )
    ,(
    2
    ,20
    ,'20150101 13:00:00.000'
    )
    ,(
    2
    ,30
    ,'20150101 14:00:00.000'
    )
    ,(
    3
    ,30
    ,'20150101 12:00:00.000'
    )

SELECT a.Product_ID
    ,p.Product
    ,a.SaleAmount
    ,SUM(b.SaleAmount) AS RunningTotal
    ,a.SaleDate
FROM #Sale AS a
INNER JOIN #Product AS p ON a.Product_ID = p.Product_ID   
LEFT JOIN #Sale AS b ON a.Product_ID = b.Product_ID
    AND b.SaleDate <= a.SaleDate
GROUP BY a.Product_ID
    ,p.Product
    ,a.SaleAmount
    ,a.SaleDate


DROP TABLE #Sale
DROP TABLE #Product
	
Image with the text 3000 years later in giant letters

How to provide a dynamic parameter drop down of year options in SSRS

Say you’ve developed a report which returns data based on a inputted year parameter value, e.g. “give me all the sales in 2014”.

How do you provide the year options for the SSRS report?

Well there’s three ways that come to mind.

Select distinct from a date field, e.g. SELECT DISTINCT YEAR(SalesDate) FROM Sales

This would certainly provide you with all the available years but the database could have millions of sales. So it’s not too efficient.

You could manually populate years in the parameter settings in the SSRS report, kind of primitive but it would work.

But for me the robust and efficient way is the solution provided below.

The following SQL query dynamically populates an integer field in a temporary table with years. The query uses a base year variable, which can be set to as far back as when the required data fields and values existed in the database. A loop then provides the years up to and including the current year. This query can be used to generate a dataset for an SSRS report and then this dataset can then be used to provide parameter values for the report. The report will then always create a list of years between the base year and the current year. Meaning the years parameter will never need to be adjusted again.

/*Create temp table populated with the years from a base year to the present year*/
IF OBJECT_ID('tempdb..#availableYear') IS NOT NULL
    DROP TABLE #availableYear

CREATE TABLE #availableYear ([Year] INT)

DECLARE @baseYear AS INT
DECLARE @i AS INT

/*Change the base year to the earliest year the database has the required data available*/
SET @baseYear = 2013
SET @i = 0

WHILE @i <= YEAR(GETDATE()) - @baseYear
BEGIN
    INSERT INTO #availableYear
    SELECT @baseYear + @i

    SET @i = @i + 1
END

SELECT * FROM #availableYear

Within the Stored Procedure that populates the report you can then do something like below to make sure the date range matches the year chosen.

DECLARE @yearChosen AS INT

SET @yearChosen = 2013

DECLARE @startDate date
DECLARE @endDate date

SET @startDate = CONVERT(CHAR(4), @yearChosen) + '0101'
SET @endDate = CONVERT(CHAR(4), @yearChosen) + '1231'

PRINT @startDate
PRINT @endDate    

--OR For example

YEAR(SaleDate) = @yearChosen

How to run multiple SQL scripts automatically in order.

If you’ve been working towards a new deployment to a live database chances are you have written several scripts (possibly dozens) that have been developed/tested against the the development server.

Now the time has come to put the update live. Which would require executing each script against the live database.

This task can be automated by using a very handy batch script to run against the directory the files are saved in.

Caveat: This process does not take into account error handling or rollbacks, it’s just a simple example people can build on.

In order for this to work the files must have been named in a manner that the necessary order of execution corresponds to ascii sort order, i.e. 001_CreateTable.sql, 002_PopulateTable etc. This is standard practice for sql file naming conventions.

Simply create a .BAT file with the following command:
(Swap servername and databaseName for your required server and database names, TIP: SELECT @@servername can provide you with the full server name.)

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
pause

Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and the command will loop and execute every SQL script in the folder.

Creating Clustered, Nonclustered Indexes on a Temp Table

One of the best features of temp tables is that an index can be applied to them.

To clarify temp tables start with #, exist within the tempdb and are accessible within child batches (nested triggers, procedure, exec calls) of the query.

The execution plan can also determine the relevant statistics regarding their operation and suggest means of optimisation and will often suggest applying an index to the table.
Below is a simple example of applying both clustered and nonclustered indexes to the temporary table #Apostle created from a CTE (Common Table Expression).

 

IF OBJECT_ID('tempdb..#Apostle') IS NOT NULL
	DROP TABLE #Apostle;

WITH CTE
AS (
	SELECT 1 AS ID
		,'Simon Peter' AS NAME
	
	UNION
	
	SELECT 2
		,'James'
	
	UNION
	
	SELECT 3
		,'John'
	
	UNION
	
	SELECT 4
		,'Andrew'
	
	UNION
	
	SELECT 5
		,'Philip'
	
	UNION
	
	SELECT 6
		,'Thomas'
	
	UNION
	
	SELECT 7
		,'Bartholomew'
	
	UNION
	
	SELECT 8
		,'Matthew'
	
	UNION
	
	SELECT 9
		,'James'
	
	UNION
	
	SELECT 10
		,'Simon'
	
	UNION
	
	SELECT 11
		,'Thaddaeus'
	
	UNION
	
	SELECT 12
		,'Judas'
	)
SELECT *
INTO #Apostle
FROM CTE

-- CREATE INDEXES
CREATE CLUSTERED INDEX IDX_CLUSTERED_ID ON #Apostle (ID)

CREATE NONCLUSTERED INDEX IDX_NONCLUSTERED_ID ON #Apostle (ID)

 

Microsoft defines clustered and nonclustered indexes as the following:

Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

  • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

Source

To learn more about indexes this is a good video on the topic.

How to export a table as an XML file using a SQL script

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.