Adding an identity value with SELECT INTO
When copying data into a new table using SELECT INTO it can be useful to add an identity column at the same time, especially where the source data does not already have a primary key. This tip describes how to do that.
See full article »
.
How to Add, Delete or Rename a Column in a Table Using SQL
In many circumstances columns in a table can be removed, added or renamed without having to drop and recreate the table. This tip describes how to do this using SQL commands such as ALTER TABLE and sp_rename.
See full article »
.
Checking the disk space used by each database on a server
It can useful to periodically check the amount of disk space used by each database on a server. This tip shows how to do this using sp_spaceused system stored procedure and also by querying the sys.databases and sys.master_files system views.
See full article »
.
Using SQL to convert row values to a single concatenated string
Most solutions to the problem of concatenating rows into a single string involve some sort of looping, usually using a cursor or while loop, or recursion using a Common Table Expression (CTE). This tip describes an alternative technique which uses an undocumented Microsoft feature to achieve concatenation.
See full article »
.
Determining the length of a text column
The length of a varchar column can be determined using the len() function, however this generates an error when used with the text datatype. Fortunately we can use the datalength() function to work out the length of a text field. This article shows how.
See full article »
.
Determining which node a clustered SQL Server is running on
The SQL Server Properties window contains a property which shows whether a SQL Server is in a failover cluster. Unfortunately it doesn’t show the node that the SQL Server is currently running on. However this is readily available from a SQL command by checking a SERVERPROPERTY. Further clustering information is available from a Data Management View (DMV).
See full article »
.
Dropping a temporary table
A temporary table will get automatically dropped at the end of the session that created it. Often this means that you won’t need to worry about dropping temporary tables, however there are occasions where you might want to explicitly drop the table.
See full article »
.
Formatting a number with thousand separators
Sometimes it can be useful to add thousand separators (commas) to large numbers to make them easier to view. There is no simple way to do this in SQL for an int and bigint, but it can be achieved by converting to a money type first. This article explains how.
See full article »
.
Listing all tables in a database and their row counts and sizes
To get the number of rows in a single table we usually use SELECT COUNT(*) or SELECT COUNT_BIG(*). This is quite straightforward for a single table, but quickly gets tedious if there are a lot of tables, and also can be slow. Here are a few ways of listing all the tables that exist in a database together with the number of rows they contain.
See full article »
.
Moving database files to a different location
Sometimes it’s necessary to move the files associated with a database (.mdf, .ldf and .ndf) to a different drive or folder. Fortunately this is fairly easy to achieve, although it does require all users to be disconnected from the database. Here are two ways of doing this using either ALTER DATABASE or the sp_detach_db and sp_attach_db system stored procedures.
See full article »
.
Determining the last time SQL Server was started
One notable omission from the server properties available from the SERVERPROPERTY() function is the date and time that SQL Server was last started or restarted. Hopefully this will be resolved in a future version but in the meantime here are a few ways to determine this date. They all work on SQL Server 2005 and 2008.
See full article »
.
Searching the SQL Server logs using SQL
SQL Server logs can be searched using SQL as an alternative to using the Log File Viewer in SQL Server Management Studio. This method uses an undocumented, but widely used, system stored procedure called xp_readerrorlog. This can be a lot quicker than loading the error log into the Log File Viewer.
See full article »
.
See what queries are currently running
Sometimes it is useful to see what is currently running on a SQL server. On SQL Server 2005 and later this is fairly easy to achieve using the Dynamic Management View (DMV) dm_exec_requests.
See full article »
.
sp_who2 - filtering and sorting the results
Sp_who2 is one of the most useful and widely used stored procedures, along with its predecessor sp_who. However it is also one of the most frustrating as it only takes a single parameter and the results cannot be ordered. For a large server with a lot of connections this can be a real nuisance. This article explains how to sort and filter the results.
See full article »
.
Splitting the query window in SQL Server Management Studio
SQL Server Management Studio (SSMS) has a handy feature which allows you to split a query window in two. This tip explains how.
See full article »
.
Adding a db_executor role
SQL Server has several fixed database roles such as db_datareader and db_datawriter, which grants the user read and write access respectively to all the tables in the database. Curiously there is no role to grant a user permission to execute stored procedures, but fortunately this is easily created as explained in this tip.
See full article »
.
Searching a string for the last occurrence of a given string
CHARINDEX provides an easy way to search for the first occurrence of a string in another string. By reversing the order of the string it can also provide a straightforward way of finding the last occurrence of a string. This can be used in conjunction with the RIGHT function to extract the part of a string that is after the last occurrence of a given delimiter
See full article »
.
Using Execute As with Stored Procedures and Dynamic SQL
In most situations it is sufficient to grant a user 'EXECUTE' rights to a stored procedure in order to run it, however if the stored procedure contains dynamic SQL an error can occur even if the user has sufficient rights to run the stored procedure. Fortunately we can use "Execute As" to resolve this.
See full article »
.
SSRS : Changing how a date and time is displayed to UK
(dd/mm/yyyy) format
SSRS may not display dates and times in the format you want. Fortunately if you want to set an explicit format (such as dd/mm/yyyy) for a field on a report then it’s straightforward to do this, without using any code or expressions. In this tip I’ll show how to set this up for an example report.
See full article »
.
Moving a Table to a Different Filegroup
By default a database has a single filegroup called PRIMARY and all tables are stored in this filegroup. However it’s possible to create other filegroups and store tables in these new filegroups. One of the most common reasons for doing this is if you have some high performance disks and want to store certain tables on those faster disks. This is especially useful for data warehouses and other large databases where you might have some data that is accessed more frequently and where response times are particularly important. This article shows how to create another filegroup and move existing tables into the new filegroup (if you are creating new tables then you can just create them on the new filegroup). I have used the AdventureWorks2008R2 demo database which can be downloaded from the CodePlex website.
See full article »
.
SQL Server TCP Port vs TCP Dynamic Ports
The default instance of SQL Server will listen on port 1433 (unless you’ve changed it to something else). However, by default, named instances will use a dynamic port, and SQL Server Browser will determine which port has been allocated and direct traffic accordingly. This works well if you’re running multiple instances on your local machine, but is not so good in corporate and other environments where you need a fixed port number so you can open specific firewall ports. Fortunately it’s fairly easy to change to a fixed port (or to change the port number) once the instance has been installed.
See full article »
.
Checking Whether Connections to SQL Server are Encrypted
This article outlines how to check whether connections to a SQL Server instance are encrypted. Ideally for data security all connections should be encrypted but sometimes this isn’t possible or hasn’t been set up. This article outlines some queries that can be run to check which current connections are encrypted.
See full article »
.
Converting row values in a table to a single concatenated string
This article covers a number of techniques for converting all the row values in a column to a single concatenated list. For example a table might contain 8 rows which requires converting to a single comma separated string containing the 8 values. The article outlines six different ways of doing this utilising loops, the CLR, Common table expressions (CTEs), PIVOT and XML queries.
See full article »
Why IsNumeric() is no good
We all know what IsNumeric() does, don’t we ? According to the Books Online definition "ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0."
See full article »
An Introduction to Change Data Capture (CDC)
Change data capture (CDC) is a method for tracking changes to data values in tables. This is useful if, for instance, you want to identify which rows of data have changed in order to load only changed rows into a data warehouse. In this short article I'll show how to set up CDC to track data change. This takes just two lines of code !
See full article »
Top 10 SQL Server Management Studio (SSMS) Tips and Tricks
I thought I’d share a few tips and tricks that I’ve picked up along the way with SQL Server Management Studio. These are some of the features which I’ve found to be useful which are perhaps less known. I’ve not included any T-SQL features as this article is specifically about SSMS features. These are my top features but if you regularly use SSMS I'm sure you will know of at least some of these features, I'd also be interested to know of any other useful features that you use.
See full article »
Amazon RDS SQL Server Hosting - Pros and Cons
Hosting a SQL Server database on an Amazon RDS server can provide a very cost effective hosting solution, with server prices starting at about $300 per year. Add to that the fact that Amazon take over responsibility for keeping service packs up to date, doing database backups, DR (with automatic multi data centre failover), virus checking, hardware maintenance etc and it’s no wonder that many firms are seriously looking at moving their database infrastructure to the AWS cloud. This article outlines some of the downsides and issues to be aware of before making this move.
See full article »
Pros and Cons of Transparent Data Encryption (TDE) Part 1 of 3
Transparent Data Encryption (TDE) encrypts all the data that’s stored within the database’s physical files and also any backup files created from the database. With data security becoming more and more important there’s no doubt that encryption of data using technologies such as TDE will become increasingly relevant. However as always there’s a price to be paid for implementing TDE and this article discusses some of the pros and cons. This is the first part of a three part series about TDE. In the other articles I’ll discuss how to set up TDE and some best practices.
See full article »
Setting up Transparent Data Encryption (TDE) Part 2 of 3
Transparent Data Encryption (TDE) encrypts all the data that’s stored within the database’s physical files and also any backup files created from the database. Encrypting a database with TDE is a very straightforward process, involving 3 simple steps. This article shows how to do this. This is the second article in a series of three around TDE.
See full article »
Best Practices for Transparent Data Encryption (TDE) Part 3 of 3
This article discusses some best practices and recommendations for implementing TDE and is the 3rd and final article in this series. Transparent Data Encryption (TDE) encrypts all the data that’s stored within the database’s physical files and also any backup files created from the database. With data security becoming more and more important there’s no doubt that encryption of data using technologies such as TDE will become increasingly relevant.
See full article »
Amazon RDS – Creating a native (.bak) Backup of a SQL Server Database in RDS
The original release of RDS had no ability to create a native database backup or to restore from a backup (i.e. a .bak file). This was a major restriction as it meant that a database could only be moved in or out of RDS by scripting it up using tools such as the SQL Server Import Export Wizard. This all changed in July 2016 when AWS announced that they had implemented support for native backups.
See full article »
Registered Servers vs Central Management Servers
If you, like me, look after a large number of SQL Servers it can be very time consuming to reconnect to these servers every time you start SQL Server Management Studio. This is where Registered Servers and Central Management Servers come into play – you can set up server information (server name and login information) just the once and then use this stored information every time you start SSMS. The servers can be set up as a single list of servers, or you can set up a hierarchy with similar servers grouped together. I tend to group servers together by the type of server (production, test, development etc.), but you can equally well group them together by application, project, data centre etc.
See full article »
Amazon RDS - Common Issues with native SQL Server backup and restore
In my introductory article I outlined details of how to create and restore SQL Server backup files to and from on-premise SQL Servers. In this follow up article I’m going to discuss some common issues I’ve experienced when both backing up and restoring RDS databases.
See full article »
Checking the Version and Build Number of an SSIS Package
When installing a new version of an SSIS package it's useful to verify what version and build has actually been installed and also what version and build it replaces. With much earlier versions of SSIS (e.g. 2008 R2) the currently installed version was very easy to check using a report within SSMS (SQL Server Management Studio). However since 2012 this is not available in a properties window or as report within SSMS but it can be checked by running a query against the SSISDB database. This article shows how to get the current installed version and build number of a package, and also how to compare it with the version in an ispac or dtsx file.
See full article »
COALESCE issues
This blog discusses an issue with the data types returned by the SQL COALESCE statement which might at first site look like a bug, but is actually part of the documented behaviour.
See full article »
Creating a copy only backup to avoid breaking a backup chain
This article explains how to create a SQL Server ‘Copy Only’ backup to avoid breaking a backup chain. This can cause problems if an adhoc backup has been done and a restore is required before the next full backup. This article explains the purpose of a copy only backup.
See full article »
Creating an index on a table with no primary key
Most tables will have a primary key defined however if a table doesn’t then it can cause unexpected table scanning when running a query, even if a covering index has been defined for the query. This blog explains why this occurs and how to get around this.
See full article »
When delete from table works but truncate table does not
Delete From and Truncate Table can both be used to empty a table of all rows, but the commands behave differently when a foreign key constraint has been defined on the table. This blog explains the differences.
See full article »
Techniques for Creating a List of the Columns in a Table
It can be useful to create a comma separated list of all the column names in a table, i.e. Column1,Column2,Column3 and so on. This can then be used in SQL insert and select statements etc. For tables with lots of columns this can be quite time consuming to write manually, this article describes several techniques for automating this process.
See full article »
Determining the Version of SQL Server
Here are a couple of ways of checking what version and edition of SQL Server is running on a server using TSQL, by utilising the @@VERSION global variable and the SERVERPROPERTY function.
See full article »
Including the Date in a Filename in SSIS using an Expression
When creating a file in SSIS it can be useful to incorporate a date and/or a time stamp into the name of the file, for instance 'File_20121225.csv'. Fortunately it's fairly easy to achieve this using expressions.
See full article »
Checking when a database was last backed up
It can be useful to periodically check when each database on a server was last backed up. In this blog I've provided SQL scripts that will check the time each database on a server was last backed up. I've also included a script that will identify any databases that have never been backed up, and another one that will list databases where the transaction log isn't being backed up regularly.
See full article »
Searching all Columns in all Tables in a Database
This blog outlines a technique for searching for a specific string in all fields (i.e. all columns in all tables) in a database. This can be useful, for instance, if you know that a certain configuration setting exists but you don’t know where it is stored in the database.
See full article »
Copying logins and passwords from one SQL Server to another
Moving SQL Server logins between servers can be difficult if you don’t know what the login passwords are, but want to maintain the same passwords on the target server. Fortunately Microsoft provide a stored procedure, sp_help_revlogin, which make this easy to accomplish. This stored procedure enables you to script up existing logins and then transfer them to another server, with the same passwords. However, if your server only has Windows Authentication logins this issue won’t be of relevance because all of the passwords will be maintained by Windows.
See full article »
Common Issues with the SQL Server Import and Export Wizard
The SQL Server Import and Export Wizard is a useful tool for copying data from one data source (e.g. a SQL Server database or Excel spreadsheet) to another. Although the interface is fairly simple there are a few “gotchas” to be aware of. This blog covers a few issues I’ve found when copying data into a SQL Server database from a similar database on another server.
See full article »
Scripting all Agent Jobs Using SQL Server Management Studio
It’s possible to script up all the agent jobs on a database instance, or a selection of the agent jobs using SQL Server Management Studio and the Object Explorer Details screen. Where there are lots of jobs to be scripted this can be a lot quicker than right clicking each job and individually scripting each one.
See full article »
Formatting an SSIS Configuration File
If you’ve used XML configuration files in your SSIS projects you’ve probably noticed that that the file is formatted as one long string on a single line. This makes the file quite difficult to read and edit. Fortunately this is very easy to resolve within BI Development Studio (BIDS), just open the configuration file (i.e. the dtsConfig file) and press CTRL K followed by CTRL D. The XML file will then be displayed on multiple lines with appropriate indentation.
See full article »
Deleting all Rows in all Tables
There may be times when need to clear down all the existing data in a database. However if you try and delete the data in each table in turn, then the chances are that sooner or later you will encounter a failure due to a constraint. One way to get around this is to temporarily disable all constraints, delete the data and then re-enable all the constraints. This article outlines how to do this.
See full article »
Preserving Logging Data When a Transaction Rollbacks
It can be useful to log progress of a sequence of SQL statements in a database table, however if there is a transaction and it gets rolled back then this will also roll back the logging table. This article outlines a way of avoiding this using table variables, which don’t get rolled back with a transaction.
See full article »
SQL Server Interview Questions for a DBA or Developer
Here is a selection of SQL interview questions together with answers that would be of use for both SQL Server developer and DBA positions. The questions are designed to test understanding of SQL Server rather than being a test of memory or recall. They should be of interest to both candidates and interviewers. The answers given are the aspects that an interviewer should be looking for.
See full article »
Orphaned Users vs Users Without Logins
Users without logins are sometimes confused with orphaned users, however these two types of users are quite different. A user without login is a special type of user that has deliberately been set up without an associated login. In contrast an orphaned user is one where the user is not currently associated with a login, most commonly because a database has been restored from another server and the association with the login has either been lost or the login does not exist on the new server.
See full article »
Connecting to multiple servers in a Query Window using SQLCMD
By switching on SQLCMD mode in SQL Server Management Studio you can add some useful extra scripting functionality that is not available with T-SQL. One particularly useful feature is the ability to switch between different SQL Servers within a query window. Without SQLCMD the usual way to do this is to bring up the ‘Connect to Database Engine’ window (for instance by right clicking and selecting ‘Change Connection’). This can become quite tedious, especially when there are several SQL Servers to connect to. Once SQLCMD is enabled this can be scripted within the query window.
See full article »
How to identify SQL Agent jobs missed during server downtime
This article shows a way of determining which SQL agent jobs have been missed during a period of scheduled server downtime. For some jobs, such as an hourly transaction log backup job, this is not a particular problem because the backup will be run at the next scheduled time once the server has started up again, and SQL Server will just catch up. However for other jobs, such as a daily report or data extract it is likely to cause more of a problem. This article includes a way of determining which jobs have been missed based on a SQL script.
See full article »
Using the SQL Server 2012 Lead and Lag Functions
SQL Server 2012 introduces several new analytic windows functions, amongst them are Lead and Lag, which can be used to reference data in other rows without the need for a self join. This article describes how they can be used in practice.
See full article »
SQL BITS Conference - London 2018
The SQL BITS team have just announced the date of the next SQLBITS conference which is from 21st - 24th February 2018 at Olympia, London.
See full article »
Clearing entries for failed jobs in the SQL agent history
SQL Server agent keeps a log of events in a table, this is used for the ‘View History’ which displays events for a job in the Log File Viewer. This script will delete selected rows from the history table, such as failed job runs.
See full article »
Creating a comma separated list of all columns in a table
This SQL script creates a comma separated list of all columns in a table. This can be useful for creating SQL INSERT and SELECT statements etc.
See full article »
Checking for unused indexes
SQL Server provides a lot of information in the Data Management Views (DMVs). This script shows how to identify indexes which are never actually used, by using DMVs such as dm_db_index_usage_stats and dm_db_index_operational_stats.
See full article »
Checking the status of SQL Server Agent jobs
As a DBA it’s important to quickly identify any issues with SQL Server Agent jobs. This article includes a number of scripts which make it easy to identify which jobs have failed and which are still running as well as generating a history of when each job ran.
See full article »
Checking for users with sysadmin privileges
One thing I like to do occasionally, or when "inheriting" a new SQL server is to check which users have sysadmin privileges. As these users have access to perform any activity in SQL Server it’s important to keep the number of these users to a minimum especially on production systems.
See full article »
Checking For Unused Database Files
It's useful to periodically check if there are any old database files that can be deleted from a SQL Server. Usually database files will be deleted along with the database, however this is not always the case. This script will identify unused files.
See full article »
Searching ALL SQL Server Logs using TSQL
The stored procedure xp_readerrorlog (and also sp_readerrorlog) allows searching of the SQL Server error logs. However only one log can be searched at a time. This script searches all available logs, by looping through each log in turn.
See full article »