Grouping Set (SQL Server 2008)

People cannot help appreciating GROUP BY clause whenever they have to get a DISTINCT from any result set. Additionally whenever any aggregate function is required GROUP BY clause is the only solution. There has always been requirement get these aggregate function based on different set of columns in the same result set. It is also safe to use this feature as this is an ISO standard.

Though the same result could be achieved earlier but we have to write different queries and would have to combine them using UNION operator. The result set returned by GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping set.

To understand it completely first we create a table tbl_Employee.



Now we populate table with some the following rows:



After populating with the rows, we select some rows using Grouping Sets.



The result of this statement is as follows:



You can see that the result set contains rows grouping by each set in the specified Grouping Sets. You can see the average salary of employees for each region and department. You can also appreciate the average salary of employees for the organization (NULL for both Region and Department). This was the result of empty Grouping Set i.e. ().

Before 2008, if you had to get the same result set, following query had to be written:



By looking at the above query, you can appreciate the ease provided by Grouping Set to the developer.

CUBE Sub Clause for Grouping
This is used to return power ‘n’ to 2 for ‘n’ elements.



The above query is equivalent to the following query:



ROLLUP operator for Grouping
This is used to return ‘n+1’ grouping sets for ‘n’ elements in the hierarchy scenario.



This is equivalent to the following query:

Object Search (SQL Server 2008)

It has always been difficult to find out the exact object if we don't exactly know the name or type of an object. In SQL Server Management Studio 2005, it is impossible to search any object because no such option exists in the first place. As a database professional, I don't remember the names of all the objects all the times. I may remember what the name should be like but not the exact name. If I know the exact name, I don't remember whether it is stored procedure or function (if there is no naming convention). To resolve all these problems, I need a search tool within the management studio environment.

SQL Server 2008 seems to answer our prayers by providing 'Object Search'. The feature is available as part of 'Object Explorer Details'. This window can be used independently and also with 'Object Explorer' window. This provides primitive search of database objects in SQL Server 2008. By 'primitive', I mean it has several limitation which would be felt by professionals using this tool.

To search any object, just type in the search bar in 'Object Explorer Details' window. There is wild card character available. This is same i.e. '%', which means zero or more characters. This character has the same behavior as T-SQL.



Object Explorer Details window can run in isolation or with the Object Explorer window.

Limitations:
1. It is not possible to search a particular type of object e.g. you can not search all stored procedures starting from 'CDC_'.

2. It is not possible to search negation e.g. we can not search all the objects which does not contain 'CDC_'.

3. Software professionals are generally comfortable with regular expressions for search. Regular Expressions are not supported for search.

Resource Governor (SQL Server 2008)

I have written an article about Resource Governor. A feature introduced in SQL Server 2008.

Click: SQL Server 2008 Resource Governor

SQL Server Agent Proxy

What would you have to do if you want any job step to be executing with a different credential than SQL Agent Service Account. The answer is SQL Server Agent Proxy. I have written a useful article about SQL Server Agent Proxy on codeproject. You can access the article on the following link.
Click: SQL Server Agent Proxy

SQL Server Alerts

I have posted a useful article about SQL Server alerts on Code Project. You can view it online at the following link:

Click: SQL Server Alert

MERGE Statement SQL Server 2008

Though it is very difficult for SQL Server developers to appreciate this is one of the greatest enhancement made in T-SQL in SQL Server 2008. The reason it is difficult to be appreciated from sQL server developer is that in T-SQL they have this FROM clause in UPDATE statement. But for the sake of clarity, I want to make it clear that this FROM clause has never been part of ANSI SQL. Now get this FROM clause out of your all UPDATE statements and try to redo some of your solutions using plain UPDATE statements. Sometimes you would find it nearly impossible. In all those conditions, MERGE statement can rescue you.

For the sake of simplicity, we work out an example. Let us have two tables with the following definitions.

CREATE TABLE CUSTOMER_TBL
(
CustomerID int PRIMARY KEY IDENTITY,
CustomerName varchar(500)
)

CREATE TABLE CUSTOMER_ORDERS
(
CustomerID int PRIMARY KEY IDENTITY,
OrderDetail varchar(200),
CustomerName varchar(500)
)

The greatest example of MERGE statement is a scenario in which there are two tables; One is a source table and the other is the target table. All the rows in the source table which has a record with matching keys in child tables, the information in the source tables are needed to be updated.

MERGE INTO CustomerOrders c
USING CustomerTable t on c.CustomerID = t.CustomerID
WHEN matched then update
set c.CustomerName = t.CustomerName
WHEN not matched then
INSERT (CustomerName) values (t.CustomerName)
WHEN source not matched then delete


The greatest thing is that all of these INSERT, UPDATE and DELETE operations are atomic in totality as a single MERGE statement being executed.

Change Data Capture (SQL Server 2008)

You can view my article about Change Data Capture on Code Project.

Click: Change Data Capture

Performance Improvement of SQL Server

In the very early ages we used to say that "Information is virtue" then it changed to "Information is power". In the present age of information technology, Information is not power any more because information is every where. It is the correct and timely use of it. Just take an example, there are two recruitment agencies both have unlimited resumes of job seekers in their database. Now an organization reaches to both of them and asked them to provide list of candidate suiting a particular criteria. Now the firm which has the faster mechanism to fetch the information from the database would be able to get the contract. So in the present age, only those organizations would remain to be existent which have faster access to information. This is "Survival of the fastest" scenario for an organizational market.

Today I want to discuss many things for you to improve the performance of your SQL Server based systems.

Hardware:

Use Faster Storage Technologies:
It is true that by writing efficient code, we can improve the performance. But this code has to run on hardware, so by using the efficient hardware, we can improve the performance of our systems. These days, we are using multi core processors and primary memory up to many giga bits per second. But the problem is that our database systems are based on the data stored in our persistent storage drives. The maximum speed of such devices is 7200 rpm. This proves to be a bottleneck. But being a software person, we are not concerned, generally, with the speed of our hard drives. But by just using a storage which becomes more responsive to the data requirements of our system, we can improve our systems many folds.

Having discussed about this, a question pops up in our minds. Can we increase the speed of our hard drives? The answer is YES. This can be done by using the drives which have higher speeds. Gone are the days in which the organizations had to use the magnetic storage drives because of their capacity, cost and reliability. These days’ organizations are moving towards solid state options for persistent storage. This storage have more random access ability. So they can process data much faster then their magnetic counterparts.

The only problem is that, these drives are not marketed as they deserve to be. But being an enthusiast, we should dig the technology to get the best out of it.

Software:

a. Efficient use of indexes
First thing first, Index does not mean improving performance for every operation. If there are more SELECT operations in a table, use index on columns. But if there are more inserts / updates / deletes, don't consider implementing it. This is because this would further slow down the process. These operations would not only be slow but would also create fragmentation problems.

b.Computed Columns:
This is another decision you have to take based on your scenarios. If you have more Insert / Updates then using computed columns would significantly reduce the performance of your system. Instead if your system involves more SELECT operations then use of computed columns would prove to be a step towards drastic performance improvement for your system.

c. Selecting the data types of columns in a table
This aspect is sometimes ignored whenever an improvement is discussed. Always consider the following points whenever you have to select a data type of one of your column.

• You should have very good reason not to use non-Unicode VARCHAR if you have to deal with character data. The reasons may be your requirement to store data more than 8000 bytes. There may be special Unicode characters which you have to use. Otherwise, you can do very fine with this data type.

• Always remember that integer based columns are much faster to sort any result set if used in an ORDER BY clause.

• Never use Text or BigInt data types unless you need extra storage.

• Using Integer based data type for Primary key would improve the performance compared to text based or floating point based ones.

d. Bulk Insert / Updates
Whenever Bulk Insert or updates are involved, selecting the recovery model to FULL or BULK LOGGED would slow down the process of the insert or update operation. So what you can do is that you change the recover model do your operation and then change the model back to the previous one.

Index could also create delay for these operations.

Always try to pass data as XML to your stored procedures and use OPENXML for BULK INSERT or UPDATE. This operation is then done through a single INSERT / UPDATE statement as a SET based operation. Avoid using DataAdapter's for update even if you are using updateBatchSize with it. This is because of the reasons: it is not SET based; still more than one UPDATE statements are passed to the SQL Server. This only decreases the roundtrip between your application and SQL Server. The numbers of UPDATE statements being executed on the server are the same. The maximum value for UPDATEBATCHSIZE is a limited value. But with XML option you can send the complete data of a DataTable.

e. Parameterized queries
You might have studied that compiled queries run faster. This is because the DBMS does not have to create a plan for the query for each operation. This also would help you as a precaution against SQL Injection attacks.

f. Stored procedures
Stored procedures also help in preventing the SQL Injection attacks. They also decrease the roundtrips between application and Database Server because many statements are combined in one procedure. This makes these bunch of statements as a single transaction. There are also many other benefits. But the specified ones are enough to use them as a first priority.

g. Stop lavishing use of explicit Cursors
This has been said by so many people so many times that this repetition is certainly not very necessary. I just tell you my example. Few years back I was given a stored procedure in which the previous developer had the lavishing use of the explicitly cursors. The execution time has risen to 40 minutes then because of increase of data in the database. This was expected to increase more as the data increases. By just replacing these cursors with some SET based operations; I was able to turn that execution time to 3 seconds. This was a great achievement on my part. By just looking at this example, you can find out that use of cursor is killing for the performance of your system.

Compound Assignment Operators (SQL Server 2008)

Like other .net programming languages compound assignment operators are introduced in SQL Server 2008. If you want to do any basic arithmatic operation (+, -, *, /, %) with any variable and simultaneously assign it to the variable then this proves to be easier in implementation.


DECLARE @MyIntVar INT = 4
SET @myINTVar += 2
SELECT @myINTVar

Inline Initialization of Variables in SQL Server 2008

Before 2008, variables could only be assigned with a value using SELECT or SET statements. There was no way to assign any value to a variable at the time when it is declared. With 2008, Microsoft has removed this shortcoming and introduced this feature. This inline initialization can be done using literal or any function.
E.g.

DECLARE @myINTVar INT = 3
DECLARE @myVARCHARVar VARCHAR = LEFT('Shujaat',1)
SELECT @myINTVar, @myVARCHARVar

Table Valued Constructors using VALUES clause

From Today onwards, I am starting a series of Article concerning new T-SQL features introduced in SQL Server 2008. The first article of this series is as under:

Before 2008, when more than one rows were to be inserted using INSERT statement, then more than one INSERT statements were required. But with SQL Server 2008, it is not necessary because only one INSERT statement would do the work. Consider a table with three columns ID, Name and Address.


CREATE TABLE TblCustomer
(
ID int PRIMARY KEY IDENTITY,
Name VARCHAR(30),
Address varchar(45)
)

The single INSERT statement is as under:

INSERT INTO TblCustomer(Name, [Address] )
VALUES ('Shujaat', 'New Jersey'),
('Siddiqi', 'California'),
('Shahbaz', 'Los Angeles')

This can also be used for bulk insert requirements because of its atomic nature. After creating this statement in you .net code, you may pass this statement to the database. This INSERT statement is an atomic operation so there aren’t three statements being executed but only single statements in executed in the database.

This constructor may also be used for SELECT and MERGE statements. The example is as follows:

SELECT VendorID, VendorName
FROM
(
VALUES(1, 'Shujaat'),
(2, 'Siddiqi')
) AS Vendor(VendorID, VendorName)

SPARSE COLUMN and COLUMN SET

Another new feature coming up with SQL Server 2008 is SPARSE column. It provides optimal storage by reducing the storage for nullable columns. But there are also downside to it as making a column as SPARSE would put some overhead when a non-null value is read for the column. The group of columns can be setup by making a COLUMN SET while creating a table. The column set would allow all the sparse columns to be updated like a single XML column.

Intellisence support in SQL Server 2008

For those of you who are bored with SQL Server management studio of SQL Server 2005. There is a good news that the same tool has intellisence support in the 2008 version.

Congratulations!!

But this comes at some cost. If you open the profiler then you would see these statements being executed.

I just executed the statement SELECT * FROM SYS.DATABASES. But this came as a price of execution of one more statement to the server. This is not unique to the SQL SERVER 2008. All those software which support intellisence services has to query the database to get the information about the object.

So if you have many database developers this might come as a cost to the Server instance. But this would also help you reduce the cost as the database developers become more productive with the support of intellisence.

I support the inclusion of this feature and admire it.