December 16, 2014

SQL Tribal Awards


Simple-Talk and are once again hosting the Tribal Awards. Inspired by the book Tribal SQL, the awards seek to recognize outstanding members of the SQL community in 11 categories.

Voting is open now and will close on January 2nd 2015. Winners will be announced on January 20th. In the spirit of Tribal SQL, the awards will also donate £250 to the charity at the close of the event.

Link to Nomination form:
Link to more information:
Article that Saved My Bacon in 2014
Blog of the Year
Person You’d Most Like to Have as a Mentor
Best Presentation at any Tech Conference
Best Tech User Group
Best New Community Voice
Beyond the call of duty / Outstanding Contribution
Best Free PowerShell Script
Best Twitter Account To Follow
Best New Book About SQL
Best Outfit or Costume at a Tech Conference

These awards are inspired by the book Tribal SQL, published last year. Tribal SQL was created by 15 authors each contributing one chapter, and donating their royalties to a UK-based charity


December 08, 2014

Error Handling in SQL Server with THROW


Continuing our series on SQL Server 2012, today we will talk about THROW. In versions prior to SQL Sever 2012, we used @@RAISE_ERROR to generate error messages dynamically or using the sys.messages catalog.

Consider the following example

SELECT ROUND(800.0, -3)

On executing this statement, you get the following error:


because the value does not fit into the decimal data type.

You can use @@RAISERROR to raise a message

SELECT ROUND(800.0, -3)
DECLARE @ErrorMsg nvarchar(1000), @Severity int
@Severity = ERROR_SEVERITY()
RAISERROR (@ErrorMsg, @Severity, 1)

Note: The old syntax of RAISERROR syntax specifying the error number and message number got deprecated (RAISEERROR 50005 ‘Exception Occurred’). Instead the new syntax RAISEERROR(50005, 10, 1) allowed you to specify the messageid, severity and state). For new applications use THROW.

However in SQL Server 2012, there’s a better way to this without much efforts – THROW. Consider the following code

SELECT ROUND(800.0, -3)


As you can see, with just one word THROW, we were able to handle the error with grace and get a result too.


November 20, 2014

User Defined Functions in SQL Server


User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role. For example, we can call user defined function in a where clause or use a user defined function in a JOIN [Where UDF returns a result set].

SQL Server supports two types of User Defined Functions as mentioned below –

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –
  • Inline Table
  • Multi-statement Table
We will explore these functions today. I am using SQL Server 2012 for this demonstration, although you can use SQL Server 2005, 2008, 2008 R2 as well.

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –
  • Customers
  • Employees
  • Orders
  • Order Details
  • Products
Let’s start querying the above table. Open a new Query window and write the following commands –


Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad –


The above function returns an integer value. To test this function, we will write some code as shown below –


Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad –


We will test this function with different years for an employee as shown below –


Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below –


To test this example we will use a select statement as shown below –


Another example of the Inline Table Valued Function is as shown below –


To test this function, we will use different years as shown below –


We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below –


To use the Multi-Statement Table Valued function, use this code –


There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below –
  • You cannot modify the state of the database using UDFs
  • Unlike Stored Procedures, UDF can return only one single result set
  • UDF does not support Try-Catch, @ERROR or RAISERROR function
Summary – User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].

Download the source code of this article (Github)


October 30, 2014

Using SET Operators in SQL Server


In this article, we will see how to use SET Operators in SQL Server. We will look at UNION, UNION ALL, INTERSECT and EXCEPT operators.

To start with SET Operators, we will first create two sample tables in our Northwind database. The script is shown as below -

USE Northwind

    Name CHAR(1)

    Name CHAR(1)

--INSERT FOR Example1 Table

--INSERT FOR Example2 Table

SELECT * FROM Example1
SELECT * FROM Example2

In the above script, we have created two tables, Example1 and Example2 and inserted some dummy data. We will now take a look at how to select the data from both the tables using the SET Operators.

UNION Operator

Let’s take an overview of our very first operator - UNION. The UNION operator combines the result of two or more queries and returns a single result set excluding the duplicate values. The following figure shows the same output -

Write the following query in SQL Server Management Studio and observe the result -


The output of the above query is as shown here -


Now let’s use the two tables in the Northwind database with the name Dept and Emp.


We will write a query where we want to find all the unique cities and countries from Customers and Employees table -


The output is shown below -



However if you want to include the duplicate values of City and Country from Customers, as well as Employees, include ALL with UNION. The query is shown below -


Some rules to remember while working with UNION operator -

1. The number of columns must be same.
2. The data type of the columns must be same or implicitly convertible by database.


We will now take a look at INTERSECT operator. The INTERSECT operator takes the result of two queries and returns common rows which appears in both the result sets excluding the duplicate values. The following figure explains the same -

Let's try the query on our Example1 and Example2 table. The query is shown below -


The output of the above INTERSECT query is shown below -


We will try the INTERSECT on Northwind database and find out all the common Cities and countries from Customers and Employees tables excluding duplicates. The query is as shown below -


The output of the above query is as shown below -


EXCEPT Operator

We will now explore the last operator, the EXCEPT operator. The EXCEPT operator returns distinct rows from the first query which do not appear into the second result set. The following figure explains the same -


The output of the above query is shown below -


We will now try the same query with our Northwind database. We will find out all the distinct cities from Employees tables which does not appear in Customers table. The query is shown below -


The output of the above query is shown below -


And that’s it. In this article, we have seen how to use SET Operators in SQL Server. We have seen UNION, UNION ALL, INTERSECT and EXCEPT operator with some simple examples.


September 15, 2014

Memory Optimized Tables and Transactions in SQL Server 2014


In one of our previous articles “SQL Server 2014 – InMemory Table, Indexes and Stored Procedures”, we saw how to create in-memory tables and indexes. We also saw how to use stored procedures to interact with these tables.

In-memory tables are capable of storing multi-versions of a rows. When you perform transactions on these tables, internally it uses snapshot – based transaction isolation. In memory optimized tables, we always assume that the transactions will optimistically commit the operations.

As we don’t have locks, we don’t have blocking mechanism in memory optimized tables. Given these points, a question always arises that what if there are conflicts. For example, we have two transactions. The first transaction tries to write a row and the same row is getting written by another transaction as well. Then these conflicts are detected and the second transaction will always fail.

In the scenario of two transactions, you can read the old version of the row in case a transaction is occurring on the row. But if the row is not available, then the transaction simply fails.

There are number of Isolation Levels supported by transactions as described below –

· Snapshot Isolation – In this level of isolation, the starting reads are consistent and writes are always consistent.

· Repeatable Read – In this level of isolation, we have guarantee that the rows which we read during the start of the transaction, are the same at the end of the transaction. Other transaction will not be able to change them.

· Serializable – In this isolation level, if you scan the entire table at the end of the transaction before commit to check, if the new row is inserted, then the transaction will fail.

Consider few guide lines while working with Memory Optimized Table Transactions –
  • Always declare which Isolation Level you want you as described above.
  • In case of transaction failure, try writing logic for handling conflicts and validations in your transactions.
  • Try avoiding long running transactions.
In my previous article I have shown how to create Memory optimized table, indexes and stored procedure. I am using the same table “Employees” for demoing Memory Optimized Tables and Transactions.

Let’s open SQL Server Management Studio and write the below script.


In the script, the query pad 1 contains a T-SQL command which is creating a table with the name Test. We have inserted two rows in the table. We then start a transaction which will update the row from the Test table which has ID=1. We are updating the row without committing it. In the second query pad, we try to access the row which is under the transaction and hence you will not see the row unless the first transaction is either committed or rollback.

Try committing the transaction and you will see the updated row in your second query pad as shown below –

Now let’s compare the same with Memory Optimized table which we have created in the previous article. Let’s write the following script in Query pad 1 –


Now update the above row and change the city from London to New York but do not commit it as shown below –

    SET City='New York' 
    WHERE EmployeeID = 88569

Now let’s try accessing the updated row from our second query pad and see the output. You will see the older version of row as shown below –


Commit the transaction and you will see the updated row as shown below –


Let’s update the city from our first query pad without committing it. And then try to update the same row from our second query pad and then we will see the difference –


The output of the second query pad transaction execution is as shown below. The error says – The current transaction attempted to update a record that has been updated since this transaction…


The above error clearly says that the row which the current transaction attempted to update cannot commit the transaction as the row has been modified outside since this transaction started.

So, in this situation, the second transaction will always fail. We can also write a retry logic which can validate the row and then modify it.

Summary – In this article we have seen how to implement a transaction with memory optimized tables in SQL Server 2014.


August 20, 2014

SQL In the City Event


SQL in the City is a unique event organized by RedGate for database developers and administrators. This event features some of the world’s top SQL Server speakers who share their wisdom about Database Development, Administration, Delivery and Best Practices. You can also have one-to-ones with Red Gate developers, and enjoy networking opportunities with the SQL Server community. As if that wasn’t tempting enough, you can learn more about the Red Gate tools that can resolve the SQL Server issues you face every day, direct from Red Gate developers and also do some drop-in hands-on labs and try out these awesome tools.

SQL in the City 2014 will be held in London in October and Seattle in November.

Who are the Speakers?

SQL Server Most Valued Professionals (MVP), Steve Jones and Grant Fritchey will be talking about best practices for database administration, development, and delivery. 10 additional speakers join the London line-up, including SQL Server MVP Ike Ellis and Visual Studio ALM MVP Brian Randell.
In Seattle, 15 additional speakers will present, including Microsoft Certified Masters Gail Shaw and Bob Pusateri.

For the full list of speakers, you can visit their website.


August 17, 2014

List Empty Tables in SQL Server


I was recently doing a clean up of my website database. I remember creating some tables on my database but never adding any new rows to it.

Here’s a simple query to list all empty tables in your SQL Server database that uses a Dynamic Management View called dm_db_partition_stats which returns page and row-count information for every partition in the current database.

;WITH EmptyRows AS
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
WHERE [TotalRows] = 0


Note that the results from the view is only approximate. If there are any active transactions that are inserting or deleting rows, the count may not include it.


July 07, 2014

Temporary Table Variables in SQL Server


In a previous article, we explored Temporary tables in SQL Server. We can also declare Temporary Table Variables which like Temporary Tables, allow us to store temporary data. However there are some differences between the two which we will be seeing shortly.

Let’s first create a simple Temporary Table variable and insert, select and update the data from the same. The script is as shown below –


Execute the above script as a single batch and you will see the desired output. The declaration of the table variable is done with the help of ‘@’ sign.

Now try the script queries one by one. First declare the table variable. Then try to insert the data and you will receive an error as shown below –


The life time of the temporary table variable is only within the batch. So it has more limited scope compared to temporary tables.

There are other limitations which you will have to understand while working with table variable compared to temporary tables. Some of them are described below –
  • You cannot use Select * INTO statement with Table variables.
  • You cannot use sp_help stored procedure to see the metadata of table variables like we saw for temporary table in our previous article. However, if you want to see the metadata for the table variable, you can make use of Sys.Tables and Sys.Columns in the context of TempDB database.
  • When you are creating table variables using Dynamic SQL, all the statements must be declared with the dynamic statement scope. Unlike temporary table, where we can declare first and then use dynamic SQL to update, insert or select the data from the table; table variables will only work within the scope of dynamic SQL batch only.
  • If you create a table variable with Identity column, you cannot add the identity column value explicitly by using “SET IDENTITY_INSERT <TableName/TempTableName> ON/OFF”.


June 07, 2014

Does my SQL Server Database Support Compression?


Some time back, Praveen Dabade had written a nice article on SQL Server Compressed Tables and Indexes in SQL Server where he explained how compression is now supported on ‘ROW and PAGE’ for tables and indexes. However did you know that compression is an enterprise-level feature?

How do determine what Enterprise Edition features are enabled on your database? Well you can use the sys.dm_persisted_sku_features DMV to find what Enterprise Edition features are
enabled on your database.

Learn more about Dynamic Management Views (DMV’s) here

Here’s the query for the same

SELECT feature_name,feature_id

Running this query will list all edition-specific features that are enabled in the current database. Some of the database changing features restricted to the SQL Server Enterprise or Developer editions are Compression, Partitioning, ChangeCapture etc.

This DMV is also useful in situations where you are planning to move a database from a higher to a lower edition. Eg: From Enterprise to Standard edition. Running the query will tell you if there are any Enterprise Edition features enabled that may not work when you move to a lower edition.

The DMV will return no rows if no features restricted to a particular edition are used by the database.


May 26, 2014

Activity Monitor in SQL Server


Activity Monitor is a handy tool in SQL Server to quickly see performance hot spots in the server, network and database activity. One of the most commonly performed tasks in Activity Monitor is to kill a stubborn connection that will not release its resources. In SQL Server 2008, this tool has undergone a major facelift from the 2005 version and helps in tracking your SQL server performance even better than before.

To view Activity Monitor in SQL Server 2008, right-click the instance name and choose Activity Monitor or click the Activity Monitor icon on the standard toolbar.


In SQL Server 2005, Activity Monitor can be viewed in SSMS by connecting to the server with Object Explorer, expanding ‘Management’, and then double-click ‘Activity Monitor’.

Once opened, the Activity Monitor Dashboard View contains four graphs which can help identity any abnormal activity in the Database. The graphs include % Processor Time (SQL Server), Waiting Tasks, Database I/O and Batch Requests. The default graph refresh rate is 10 seconds, but you change that easily by right-clicking any of the four graphs and selecting the appropriate refresh interval.

This snapshot is very helpful to get a quick performance snapshot without the need to use other monitoring tool for the same purpose.

SQL Activity Monitor

This dashboard also contains expandable/collapsible panes to view detailed information about Processes, Resources, I/O and Expensive Queries. Just click on the expand button to the right of each pane to view the detailed information. Here’s a quick overview of the different graphs and what they show.
Processor Time - The percentage of elapsed time that the processor spends to execute non-idle threads for the instance across all CPUs. The Processes pane gives information on what processes are running, what resources are being utilized etc. You can even right-click a process and choose Trace Process in SQL Server Profiler. Presto!
Waiting Tasks - The number of tasks that are waiting for processor, I/O, or memory resources. The Resource Waits pane details the processes waiting for other resources on the server.  It shows the latest information from several DMVs like the sys.dm_os_wait_stats
Database I/O – Information on data and log files for system and user databases. Provides the transfer rate in MB/Sec, of data from memory to disk, disk to memory, or disk to disk. The pane contains information to quickly detect a contention in disk I/O.
Batch Requests/sec - The number of SQL Server batches that are received by the instance. The Expensive Query pane lets you find and tune expensive queries. You can even right-click any query and view its graphical execution plan. Also see Find the Most Time Consuming Code in your SQL Server Database
Note: To view the Activity Monitor in SQL Server, a user must have VIEW SERVER STATE permission. Also make sure you close the Activity Monitor tool in SSMS when it is not required.


Copyright © 2009-2014 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions