GROUPING SETS is a way of aggregating data to produce a result set containing different sets of columns. For the SQL Server platform, it was first introduced in SQL 2008. Prior to SQL Server 2008, achieving this type of result set was possible using UNION, WITH CUBE, WITH ROLLUP and a helper function called GROUPING to achieve similar results. The issues with WITH CUBE … [Read more...] about GROUPING SETS performance versus UNION performance
Database views explained
Database views are virtual tables which are built up using a SELECT query. The query may contain one or more tables. To help explain database views, here is a quick script created using SQL Server to create some tables and data. (If you want to create the tables using MySQL, then substitute the part "IDENTITY(1,1)" with "AUTO_INCREMENT") [sourcecode language='sql']CREATE … [Read more...] about Database views explained
How to shrink tempdb
There may come a time when you might want to shrink tempdb because it has become too large. There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which way you want to approach this. There is an important note at the end of the post. So first, we'll look at the configuration on my … [Read more...] about How to shrink tempdb
SQL Server TempDb – what’s it for?
SQL Server TempDB is a system database, automatically created when you install SQL Server. So what is it used for? Well a few things actually but first I have to tell you that Microsoft didn't name tempdb because they couldn't think of a suitable name. It is a temporary database which is re-created every time the SQL Server service is started and at a higher level, it … [Read more...] about SQL Server TempDb – what’s it for?
The importance of the foreign key constraint
The foreign key constraint is an important aspect of database design. This article explains why. Foreign key constraint advantages The purpose of the foreign key constraint is to enforce referential integrity but there are also performance benefits to be had by including them in your database design. Firstly lets look at an example of how they are used in database … [Read more...] about The importance of the foreign key constraint