Category Archives: How To

How to write T-SQL Geography data to a table

Database Administrator, dba, How To, IT, sql, SQL Server, T-SQL, tsql, , , , , , , , , , , , , ,

Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.

Note: Pass in Longitude and Latitude values in that order.

/*Demo of geo data*/
DECLARE @g GEOGRAPHY;

SET @g = GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

/*Geography data is in binary format*/
SELECT @g AS 'GeoBinaryFormat';

/*Convert binary data to a string*/
SELECT @g.ToString() AS 'ConvertingDataToString';


/*Inserting geo data into Table*/
CREATE TABLE #GeoTest ([CoordinateLocation] [geography] NULL);

INSERT INTO #GeoTest (CoordinateLocation)
SELECT GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

SELECT *
FROM #GeoTest;

DROP TABLE #GeoTest;
An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

Coding, Data, Database Administrator, Development, How To, sql, SQL Server, T-SQL, , , , , , , , , ,

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

How to set a database to single user and back to multiuser

Database Administrator, dba, How To, SQL Server, , , , , , , , , , , , , , , ,

Here’s a quick script to set a database to single user and then back to multi user.

You’ll need to do this when altering the database in situations like database name changes, file name changes or even restores 

/*
Set Database to Single User
*/
USE [master];
GO

ALTER DATABASE [my_db]

SET SINGLE_USER
WITH

ROLLBACK IMMEDIATE;
GO

/*
Set Database to back to Multi User
*/
USE MASTER;

ALTER DATABASE [my_db]

SET MULTI_USER
WITH

ROLLBACK IMMEDIATE;
GO

 

How to get the date a web page was published

How To, , , , , , , , , , , , ,

This posting is particularly useful for anyone looking to use the date a web page was published as part of academic referencing.

Step 1. In your browser open https://www.google.com/.

Step 2. Copy the url of the web page you want to get a publication date for.

Step 3. Paste this url into the search bar of the google.com page and search. The search should return a series of results.

Step 4. In the address bar of the results page go to the very end of the url text and paste &as_qdr=y15 and hit return.

This should update the results page with each link including a publication date.

How to find Missing Indexes for all databases in a SQL Server instance

Database Administrator, dba, How To, sql, T-SQL, tsql, , , , , , , , , , , ,

This script is for SQL Server 2005 and up. The script will return all the missing indexes for a SQL Server instance, rating their impact and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) and DMO (Database Management Objects) which this script requires to function.
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.

/*Script to find Missing Indexes for all databases in SQL Server*/
/*
This script is for SQL Server 2005 and up. 
The script will return all the missing indexes for a SQL Server instance, rating their impact 
and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) 
and DMO (Database Management Objects) which this script requires to function. 
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) 
operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. 
INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. 
The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to 
the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.
*/
SELECT [EstIndexUses]
	,[EstIndexImpact%]
	,[EstAvgQueryCost]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[CreateIndex]
	,[EqualityColumns]
	,[InequalityColumns]
	,[IncludedColumns]
	,[UniqueCompiles]
	,[LastUserSeek]
FROM (
	SELECT migs.user_seeks AS [EstIndexUses]
		,migs.avg_user_impact AS [EstIndexImpact%]
		,migs.avg_total_user_cost AS [EstAvgQueryCost]
		,db_name(mid.database_id) AS [DbName]
		,OBJECT_SCHEMA_NAME(mid.OBJECT_ID, mid.database_id) AS [SchemaName]
		,OBJECT_NAME(mid.OBJECT_ID, mid.database_id) AS [TableName]
		,'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN '_'
			ELSE ''
			END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN ','
			ELSE ''
			END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [CreateIndex]
		,mid.equality_columns AS EqualityColumns
		,mid.inequality_columns AS InequalityColumns
		,mid.included_columns AS IncludedColumns
		,migs.unique_compiles AS UniqueCompiles
		,migs.last_user_seek AS LastUserSeek
	FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
	INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
	) AS a
WHERE 1 = 1
--AND [EstIndexUses] > 1000
--AND [EstIndexImpact%] > 10
--AND [EstAvgQueryCost] > 1
--AND DbName IN ('DatabaseName')
ORDER BY [EstIndexUses] DESC
	,[EstAvgQueryCost] DESC
	,[EstIndexImpact%] DESC
OPTION (RECOMPILE);

 

How to schedule a job to restore the last backup made of a SQL Server database

Database Administrator, dba, How To, SQL Server, T-SQL, tsql, , , , , , , , , , , , ,

This post provides you with a script that will generate a restore script for a database using the latest full backup file that exists in a directory. (No need to state the filename explicitly)

You need to provide the following at the start of the script:

  • The target database i.e. the database you will restore to
  • The directory where the backup file is saved

If you are using the excellent Ola Hallengren maintenance solution (see link) the directory path will look something like below. If you’re not using Ola’s solution, you should be.

\\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\

This restore script is designed to work with Ola’s solution as it segregates the backup directory structure such that each database has an allocated folder and each full backup file is named with the date and time of the file creation.

The restore script determines which backup file is the latest backup file based on the max name. So for the script to work it is assumed you have an appropriate backup strategy (i.e. using Ola’s solution) were backup types are segregated into different folders, backup names have a date reference and the backup location is dedicated to backups and nothing else, i.e. no trash files in the location.

Some use cases for this solution might be:

  • Restoring a nightly backup to another instance for reporting purposes
  • Restoring backups to a development environment
  • Restoring backups to another server to test the backups

You can use the logic in a stored procedure or as the T-SQL in a job step and schedule accordingly.

/*
You need to reference the following:
* The target database i.e. the database you will restore to.
* The directory where the backup file is saved.
If you are using the Ola Hallengren backup scripts the directory path will look 
something like below.
\\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\

Find & Replace the follow text for the target database and directory:

TARGET_DATABASE
DIR_PATH
*/
/*
Declare Variables
*/
DECLARE @DatabaseToRestore AS VARCHAR(MAX);
DECLARE @DirToSearch AS VARCHAR(MAX);
DECLARE @ShellCommand AS VARCHAR(MAX);
DECLARE @BackupFile AS VARCHAR(MAX);
DECLARE @Sql AS VARCHAR(MAX);

/*
Set User Variables
*/
SET @DatabaseToRestore = 'TARGET_DATABASE';
SET @DirToSearch = 'DIR_PATH';
SET @ShellCommand = 'dir ' + @DirToSearch;

/*
Create Temp Table To Hold xp_cmdshell Output
*/
IF OBJECT_ID('tempdb..#DirList') IS NOT NULL
	DROP TABLE #DirList;

CREATE TABLE #DirList (
	Id INT identity(1, 1)
	,line NVARCHAR(1000)
	);

/*
Enable Advanced Options To Enable xp_cmdshell Temporarily
*/
EXEC master.dbo.sp_configure 'show advanced options'
	,1;

RECONFIGURE
WITH OVERRIDE;

EXEC master.dbo.sp_configure 'xp_cmdshell'
	,1;

RECONFIGURE
WITH OVERRIDE;

/*
Run The Shell Command To Capture And Write Dir Info To Temp Table
*/
SET @Sql = '
INSERT INTO #DirList (line)
EXEC xp_cmdshell ' + '''' + @ShellCommand + '''' + ';';

EXEC (@Sql);

EXEC master.dbo.sp_configure 'xp_cmdshell'
	,0;

/*
Disable Advanced Options And xp_cmdshell Again
*/
RECONFIGURE
WITH OVERRIDE;

EXEC master.dbo.sp_configure 'show advanced options'
	,0;

RECONFIGURE
WITH OVERRIDE;

/*
Get The Last Backup File Name And Save To A Variable
*/
WITH CTE
AS (
	SELECT SUBSTRING(line, 37, 100) [FileName]
	FROM #DirList
	WHERE Id > (
			SELECT MIN(Id)
			FROM #DirList
			WHERE line LIKE '%<DIR>%..%'
			)
		AND Id < (
			SELECT MAX(Id) - 2
			FROM #DirList
			)
	)
SELECT @BackupFile = [FileName]
FROM CTE
WHERE [FileName] = (
		SELECT MAX(FileName)
		FROM CTE
		);

/*
Create The Restore Script
*/
SET @BackupFile = @DirToSearch + @BackupFile
SET @Sql = '
ALTER DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' FROM DISK = ' + '''' + @BackupFile + '''' + '
WITH NORECOVERY
,REPLACE;
RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' WITH RECOVERY;
'

/*
To Directly Execute The Script Uncomment The EXEC Statement And Delete The Select Statement
*/
/*
EXEC (@Sql)
*/
SELECT @Sql

 

An icon of a computer folder with a visible file and two arrows facing up and down

How to move/rename Database files in SQL Server

Database Administrator, dba, How To, SQL Server, T-SQL, tsql, , , , , , , , , , , , , ,

An example use case for the process below could be you need to move database files to a new drive. Another example might be your organisation intends to run a legacy database along side a new updated database with both sharing the same database name in the same instance with the files located in the same directory with the same names. Obviously this cannot be done and requires the database names to differ and the files to be renamed or not exist in the same directory.

For example AdventureWorks might become AdventureWorks_Legacy while a new and improved AdventureWorks database retains the original database name. The associated database file names would also need to be changed/moved to reflect this.

Someone might also want to do something like this for test purposes but obviously having test resources in a live environment would not be recommended if avoidable.

The first step to moving and renaming the files is to copy and modify the script below. Note the script below assumes you want to move and change the names of the files. To avoid any database conflicts you only need to do one or the other.

/* 
Find & Replace DbName with the name of the Database you are working with
*/
USE [DbName];

/*
Changing Physical names and paths
Replace 'C:\...\NewDbName.mdf' with full path of new Db file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName '
	,FILENAME = 'C:\...\NewDbName.mdf'
	);

/*
Replace 'C:\...\NewDbName_log.ldf' with full path of new Db log file to be used
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = ' DbName _log'
	,FILENAME = 'C:\...\NewDbName_log.ldf'
	);

/*
Changing logical names
*/
ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName
	,NEWNAME = NewDbName
	);

ALTER DATABASE DbName MODIFY FILE (
	NAME = DbName_log
	,NEWNAME = NewDbName_log
	);
Once the script has been set up as desired follow the steps below:
  1. Open Microsoft SQL Server Management Studio (SSMS).
  2. Connect to the server that houses the Db you are working with.
  3. Run the modified script
  4. Right click on the Db in SSMS and select Tasks > Take Offline
  5. If you are moving the database files log into the server that houses the database files and copy and move the MDF and LDF files to the location you specified in first two alter commands. If the script specifies new names rename the copied files to match the names given in the script exactly.
  6. Go back to SSMS and right click on the Db and select Tasks > Bring Online.
  7. If you have moved the files once the database is back online and confirmed working as expected the unused original files can be deleted.
  8. Now you can rename the Db to the new name if you wish using SSMS.

How to determine why a T-SQL command is unreasonably slow

Database Administrator, dba, How To, sql, SQL Server, System, T-SQL, tsql, , , , , , , , , , ,

If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

 

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

  • status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
  • blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session. 
    KILL blocking_session_id /*replace by the actual Id*/

    NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

  • wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
  • wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
  • last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.

 

How to rename and/or remove tables in SQL Server with T-Sql generated by Excel formulas

Database Administrator, dba, Excel, Form, How To, Project Management, Reporting, SQL Server, tsql, , , , , , , , , , , , , , ,

This post deals with using an Excel file to generate T-Sql code to rename and/or remove tables given a scenario like the following. (To generate T-Sql to remove tables using T-sql see this post.)

Say someone sends you a list via an email or text file of tables they want renamed or removed from a database . You could go into SSMS object explorer and rename or delete each table in the list one by one. Or you could write the T-Sql statements individually but chances are you can speed things up using Excel.

With Excel you can input the schema and table name into a given cell and the T-Sql code will be generated to rename and drop the table using formulas.

To do this you can just download the Excel file template here. Download

Rename And Drop Script Generator

The template is setup assuming you are intending on the dropping the table sometime in the future but first you will be renaming it.

A good approach for removing objects is to rename the objects first. This makes it easier to put the environment back the way it was if there are any problems encountered. After a set period of time if there is no negative impact on the overall environment you can script out the object then drop it. (Obviously do this in a test environment first if possible)

To aid further in a cleanup project the Excel file also acts as a form that can be used to track progress as it contains the columns RenameDate, RestoreDate and DropDate. It also contains the column RestoreOriginalName. This column holds the formula to create the T-Sql code to renamed the tables back if there are any problems encountered.

You can adjust the formula in cell D2 to somethings other than _DELETE_ if you want to change the prefix so the tables will be renamed something else. If you just want to remove the tables you’ll have to run the script from column D before you can drop the tables using the script from column F.

Remember to drag the formula down for as many table entries as you have and it will generate the T-Sql needed.

You can create the Excel file manually yourself without downloading it.

To do so open a new Excel file and in an empty sheet name the first 9 columns as below:

A1: DatabaseName
B1: SchemaName
C1: TableName
D1: RenameForDeletion
E1: RestoreOriginalName
F1: DropTable
G1: RenameDate
H1: RestoreDate
I1: DropDate

For D2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”.”&C2&”‘, ‘_DELETE_”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For E2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN exec sp_rename ‘”&B2&”._DELETE_”&C2&”‘, ‘”&C2&”‘ END ELSE BEGIN SELECT ‘TABLE [“&A2&”].[“&B2&”].[“&C2&”] DOES NOT EXIST’ AS [RenameFailed] END;”

For F2 enter the following:

=”USE [“&A2&”]; IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘”&B2&”‘ AND TABLE_NAME = ‘_DELETE_”&C2&”‘)) BEGIN DROP TABLE [“&B2&”].[_DELETE_”&C2&”] END;”

To test that the scripts generated work you can create the mock database and table using the script below. The Excel file is loaded with these values by default.

CREATE DATABASE [TidBytez];
GO

USE [TidBytez]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customer] ([ID] [int] NULL) ON [PRIMARY]
GO

 

How to tell if you are a member of a SQL Server group or create a list of group members using T-SQL

Database Administrator, dba, How To, sql, SQL Server, T-SQL, tsql, , , , , , , , , , , , ,

The following scripts will help you determine if you are a member of a group or role or create a list of group members in SQL Server without having to use SQL Server Management Studio. This is a particularly handy script in determining who might have access to the server through Active Directory groups.

/*
The code below indicates whether the current user is a member 
of the specified Microsoft Windows group or SQL Server database role.
A result of 1 = yes
,0 = no
,null = the group or role queried is not valid.
*/

SELECT IS_MEMBER('group or role')

/* 
Example. 
*/

SELECT IS_MEMBER('db_owner')

/*
The code below will create a list of all the logins that are members 
of a group. Don't forget to include domain, e.g. admin\user
*/

EXEC master..xp_logininfo 
@acctname = [group],
@option = 'members'