Data Management - Author(s): Axel Achten (axel8s)

Why there should be SQL Content at Techdays.be

Axel Achten (axel8s) — Tue, 29 Jan 2013 07:32:00 +0000

On 5, 6 and 7 March 2013 Microsoft Belux is organizing Techdays 2013, 3 days of sessions for IT Pro's and developers. But when checking the calendar there are no sessions about SQL Server.
The reason is that the Belgian SQL Server User Group organizes yearly a 2 day event about SQL Server the SQL Server Days and this is where the SQL Server enthusiasts should get their knowledge.

But I disagree, as a full-time SQL Server consultant, I can't justify going to an event where there is no SQL content at all. I know there are some other interesting sessions about Windows Server, SCOM, etc. but they aren't the core of my job so I can't register for this event and thus I will miss the opportunity to:

learn about new features in Windows server, SCOM, PowerShell...;
network with current and former co-workers outside the office;
network with other SQL Server DBA's and developers;
enthusiasm young developers and system administrators to learn more about SQL Server;
visit the partner booths, to learn what's new in the hardware world;
bring home a bag of goodies to make the kids happy and win some awesome prices.

And how about young, all-round system administrators and developers who can't justify a visit to Techdays AND SQL Server Days because their main focus is not on SQL Server, wouldn't they like to discover the wonderful world of SQL Server and learn:

how they can tame the never ending growth of the T-log;
the developers don't need SSMS anymore
SQL Server supports Server Core
cursors are bad for SQL Server
there such things as graphical execution plans
...

Anyways that's my vision on the subject, feel free to agree or disagree in the comments, discussing this with me at the Techdays will probably be a no show.

Original post blogged on LessThanDot.

WHERE not to use FUNCTIONS

Axel Achten (axel8s) — Thu, 24 Jan 2013 12:02:00 +0000

Functions can be very powerful, but used in the wrong place in a query they can show some unexpected behavior.
In this post I will be using the AdventureWorks2008R2 database and I will query the Sales.SalesOrderHeader to get all the 2006 OrderDates. A query that doesn’t make much sense but will return some interesting results.
Take the following query:

tsql

Line number Off | Hide | Select all

USE AdventureWorks2008R2;
GO
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20060101' AND '20061231';
GO

If we take a look at the execution plan:

We see that there is a complete scan of the Clustered Index which makes sense since there is no index on the OrderDate column.
As the Missing Index Hint suggests, create an index on the OrderDate column:

tsql

Line number Off | Hide | Select all

CREATE INDEX IX_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate);
GO

Executing our first query again in another form:

tsql

Line number Off | Hide | Select all

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20060101' AND OrderDate <='20061231';
GO

Results in the following Execution Plan:

You see that the query is internally translated to SELECT [OrderDate] FROM [Sales].[SalesOrderHeader] WHERE [OrderDate]<=@1 AND [OrderDate]<=@2> just like the first query using the BETWEEN keyword. The big change is in the execution plan. Since we created an index on the OrderDate column, SQL Server is now using an Index Seek on our index to fetch the results.

Since we are looking for all the dates in 2006, you might want to consider using the YEAR function. The YEAR function returns only the YEAR part of a date(time) value:

tsql

Line number Off | Hide | Select all

SELECT YEAR('20060127 02:15:59')

Results in

So the following query should make sense and is more readable then the former 2:

tsql

Line number Off | Hide | Select all

SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2006;
GO

And looking at the result set it makes sense:

But when we look at the execution plan:

We see that our index isn’t seeked anymore but gets a complete scan. So instead of searching in some 4K rows, SQL Server is scanning more than 30K of rows. This is because SQL Server is applying the function to all of the rows in our Sales.SalesOrderHeader table before it’s compared to our desired value.

Conclusion
Be careful when using functions in the WHERE clause of a query. It’s possible that the function will be applied to all the rows before the filter is applied. Resulting in scans, non used indexes, more I/O, memory consumption and a poor performing query.

Original post blogged on LessThanDot.

Why avoiding multiple code blocks in a Stored Procedure

Axel Achten (axel8s) — Mon, 21 Jan 2013 11:45:00 +0000

It was a little remark from Bob Beauchemin (B|T) during the Belgian SQL Server Days that started me writing this post.

Showing cached Execution Plans
In this post I’m going to use information from the following Dynamic Management Views and Functions to show some information about the cached Execution Plans of the queries used in this post:

sys.dm_exec_cached_plans shows types, usage, size… of the Execution Plans;
sys.dm_exec_sql_text shows the actual code of the query;
sys.dm_exec_query_plan is used to get the XML Execution Plan itself.

In fact I use following query to get the results:

Code: tsql

SELECT cp.objtype, cp.cacheobjtype, cp.usecounts, cp.size_in_bytes, st.[text], qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle)qp;

The result set of this query looks like this:

The result set can contain thousands of rows, depending on the uptime of the server, the number of queries and their corresponding execution plans, the available buffer memory…

IF using 2 code blocks
So let’s create a simple Stored Procedure that executes against the AdventureWorks2008R2 database:

Code: tsql

USE AdventureWorks2008R2;
GO

CREATE PROCEDURE TwoPlans
@IfParameter int
AS
SET NOCOUNT ON;
IF @IfParameter = 1
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress bea
INNER JOIN Person.Address a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City
ELSE
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
GO

The results of the query aren’t important what is important is to see what happens in the Procedure Cache. To be able to see what’s happening we are going to empty the procedure cache:

WARNING: executing the following code deletes all cached plans from the Procedure Cache. All Execution Plans need to be recompiled. This can result in a slow or unresponsive server.
DON’T EXECUTE THE FOLLOWING CODE ON A PRODUCTION SERVER!!!

Code: tsql

DBCC FREEPROCCACHE;
GO

When you execute our first query again:

Code: tsql

You can see in the result set that only the Execution Plan from the above query is stored in the Procedure Cache.

Now execute the stored procedure:

Code: tsql

EXEC TwoPlans 1;
GO

Execute the query against the Procedure Cache again:

Code: tsql

The result set shows us the execution of the above query with a usecount of 2 and the Execution Plan of our Stored Procedure:

Now click the XML link in the query_plan column. A new tab will open in SSMS showing you the XML Execution Plan:

You can now read the XML plan and you will find both the statements in the query plan:

Code: xml

ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="CREATE procedure TwoPlans
	@IfParameter int
as
	SET NOCOUNT ON;
" StatementId="1" StatementCompId="3" StatementType="SET ON/OFF" />
<StmtCond StatementText="	If @IfParameter = 1
	" StatementId="2" StatementCompId="4" StatementType="COND">
<Condition />
<Then>
<Statements>
<StmtSimple StatementText="	SELECT a.City, COUNT(bea.AddressID) EmployeeCount
		FROM Person.BusinessEntityAddress bea 
			INNER JOIN Person.Address a
				ON bea.AddressID = a.AddressID
		GROUP BY a.City
		ORDER BY a.City
" StatementId="3" StatementCompId="5" StatementType="SELECT" StatementSubTreeCost="0.604708" StatementEstRows="574.696" StatementOptmLevel="FULL" QueryHash="0x03E92D79FC617C86" QueryPlanHash="0xED13B89036D1A5E6" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="32" CompileTime="7" CompileCPU="7" CompileMemory="344">…
</StmtSimple>
</Statements>
</Then>
<Else>
<Statements>
<StmtSimple StatementText="	Else
		SELECT DATEPART(yyyy,OrderDate) AS N'Year'
			,SUM(TotalDue) AS N'Total Order Amount'
		FROM Sales.SalesOrderHeader
		GROUP BY DATEPART(yyyy,OrderDate)
		ORDER BY DATEPART(yyyy,OrderDate);" StatementId="4" StatementCompId="8" StatementType="SELECT" StatementSubTreeCost="0.780189" StatementEstRows="4" StatementOptmLevel="FULL" QueryHash="0xA73814A2D4649412" QueryPlanHash="0x7A5BDE1102728DAA" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">…
</Statements>
</Else>
</StmtCond>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

An easier trick to see the Graphical Execution Plans is to open SQL Sentry Plan Explorer and copy the XML into the Plan XML tab:

After doing this SQL Sentry Plan Explorer will give you all the details about the Execution Plan and you’ll see in the Plan Diagram that SQL Server created an Execution Plan for both the queries altough only the first one was used:

IF calling 2 Stored Procedures
Now let’s create 2 Stored Procedures that each will execute 1 part of the code from the previous query:

Code: tsql

CREATE PROCEDURE EmpCntCity
AS
SET NOCOUNT ON;
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress bea
INNER JOIN Person.Address a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;
GO

CREATE PROCEDURE OrderAmountYear
AS
SET NOCOUNT ON;
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
GO

Next we create a Stored Procedure that will contain the IF…ELSE logic and based on the input parameter will execute one of the above Stored Procedures:

Code: tsql

CREATE PROCEDURE TwoProcs
@IfParameter int
AS
SET NOCOUNT ON;
IF @IfParameter = 1
EXEC EmpCntCity
ELSE
EXEC OrderAmountYear;
GO

Now we can call our Stored Procedure and execute 1 of the Stored Procedures:

Code: tsql

EXEC TwoProcs 1;
GO

Let’s again query the Procedure Cache:

Code: tsql

And have a look at the result:

Now we see that executing the second Stored Procedure also created 2 Executions Plans: 1 for the Stored Procedure “TwoProcs” and one for the Stored Procedure “EmpCntCity” that was effectively executed. But we see that there was no Execution Plan created for the Stored Procedure “OrderAmountYear”. And this Execution Plan was more complex than the Execution Plan of our “TwoProcs” Stored Procedure. We can also see that the sum of the sizes of the 2 Execution Plans (65536 + 16384 = 81920) is still smaller than the Execution Plan of the “TwoPlans” Stored Procedure (98304).

Conclusion
Avoid Stored Procedures that contain complete code blocks encapsulated in IF...ELSE or CASE blocks. It will result in SQL Server creating Execution Plans for all possibilities, consuming more Buffer Cache (memory) and in the end slow down the execution of the code.
As a bonus, troubleshooting the individual Stored Procedures will be much easier and there is a bigger chance that you can reuse the Stored Procedures.

Original post blogged on LessThanDot.

Getting started with Execution Plans

Axel Achten (axel8s) — Thu, 10 Jan 2013 07:52:00 +0000

Another document for the customer's DBA checklist. This time we are going to look how we find and read an Execution Plan from a query.

Showing an Execution Plan
I will only focus on the graphical Execution Plans and in the examples the AdventureWorks2008R2 OLTP database is used which you can download here.
To see a Query Execution plan, you need to create a query:

Code: tsql

SELECT * FROM Sales.vStoreWithContacts

And then you click the "Display Estimated Execution Plan" or the "Include Actual Execution Plan" button in the SQL Editor Toolbar of SQL Server Management Studio.

The difference between the Estimated and the Actual plan is the execution of the query itself. When you hit the "Display Estimated Execution Plan" button, SQL Server Management Studio will immediately show you an Execution plan, however it's an estimated plan and the query itself is NOT executed. When you hit the "Include Actual Execution Plan" nothing will happen until you execute the query. After execution of the query a third tab will appear in SSMS showing the Execution Plan that was actually used:

Reading Execution Plans
When you want to read the above Execution Plan you have to read from right to left and from bottom to top. So SQL Server will start with

And end with

Other information you directly can see is the most expensive operation and based on the thickness of the connecting arrows you can see the amount of data thatï¿½s streaming through them:

Finding more information
When you hover your mouse over the arrows or operations in an Execution Plan a pop-up will appear giving you more detailed information about the rows or operation:

Needless to say that the Actual Number of Rows and some other information will not be available in an Estimated Execution Plan. In the example above we see a big difference between the Estimated and the Actual Number of Rows which might indicate that our statistics are out of date.
For even more information select an operation and open the Properties Window (F4):

Another option to get more out of your Execution Plans is to download SQL Sentry Plan Explorer more information is found at their site but here is a screenshot from the same Execution Plan in the Free Plan Explorer:

As you can see, the most expensive operation is highlighted in red and the tool also highlights the difference between the estimated and actual rows.

Missing index information
When executing the following query:

Code: tsql

SELECT * FROM Sales.vStoreWithContacts
WHERE Firstname = 'Alan'

You’ll see some green text in the top of the Execution Plan:

This information comes from the Dynamic Management View: sys.dm_db_missing_index_details. More information on Indexes is out of scope for this post but be careful with just implanting these Missing Indexes. They might improve the performance of this query but since they need to be maintained they can also slow down other operations on the database.

Comparing Execution Plans
Are you rewriting a query and wondering which one should perform better? You can easily compare the Execution Plans of two queries. Just write these queries in one query window:

Code: tsql

SELECT * FROM Sales.vStoreWithContacts
WHERE Firstname NOT LIKE 'A%';

select * FROM Sales.vStoreWithContacts
WHERE FirstName IN (SELECT FirstName FROM Sales.vStoreWithContacts
WHERE FirstName NOT LIKE 'A%');
GO

When you execute the queries you’ll see they both return the same 70 rows but when you look at the header of the Execution Plans you’ll see that the first query costs less than the second one in this batch:

Conclusion
Execution Plans gives us valuable information and a deep insight in how SQL Server executes the queries we write. Reading and understanding Execution Plans are essential for writing well performing queries.

Original post blogged on LessThanDot.

Database Projects - Installing the Database Project Template

Axel Achten (axel8s) — Tue, 08 Jan 2013 12:10:00 +0000

This is the second post in the Database Projects series. The first post was Setting up Source Control.
So now we have Source Control in place we need to install the Database Projects Template. To do this launch SQL Server Data Tools 2012 (SSDT). In the Start Page, choose for a New Project:

When you want to create a Database Project for the first time, you'll see a Web Install in the New Project Template screen when you select SQL Server:

When you click the Web Install, a new pop-up screen will appear telling you you have to install Microsoft SQL Server Data Tools. Off course this is what you want so hit install:

A web page will open where you can download SQL Server Data Tools for Visual Studio 2010 or for Visual Studio 2012. Since I'm using the SQL Server Data Tools delivered with SQL Server 2012 and this is a Visual Studio 2010 Shell I will download the Visual Studio 2010 version:

Another screen will show, giving you more details about what's going to happen and give you more options if you need to install SQL Server Data Tools on a machine with no internet access or with other language needs. Since Dutch is not supported and my machine has internet access I just start the download:

Now you can choose to Save or Run the setup. I'm just going to run it:

Don't know if you can trust Microsoft Corporation as a Publisher but hitting Don't Run will not help you on getting the software installed. So hit Run:

Now we need to accept the License Terms to install:

Now this is why you have to admire sysadmins, allow the software to make changes to your computer:

Finally you can take a nap while the download runs and the installer starts installing.
After the Setup finish you can Close the installer:

Close SQL Server Data Tools and start it again. Because of the new version you might get the question what Default Environment Settings you want. I chose SQL Server Development Settings before starting Visual Studio.

.
I still don't understand why the SQL Server Database Project Template isn't delivered with the initial setup of the SQL Server Data Tools but we're almost ready to create our first project.

Original post blogged on LessThanDot.

Why you shouldn't use SELECT *

Axel Achten (axel8s) — Wed, 02 Jan 2013 11:28:00 +0000

A customer's DBA team created a checklist for the development teams with some best practices for writing proper T-SQL and asked me to write some contributions for their tips document library. So if I do the research and write the documents I might as well post them here. The content may not be all sparkling and new but since there is a demand from customers, there are still people out there having trouble finding the correct information.
So let's get started. The first document is why you should avoid SELECT * in your queries:

For performance reasons

To be honest this part of the post is based on Ted's post: SELECT * vs SELECT 1 with EXISTS.
To check the performance impact I use a tool called SQLQueryStress to execute my queries and see how much time elapsed.
To get started I need a table with a large number of columns, data is not necessary for this test so I use this script to generate a table with 127 columns:

Code: tsql

DECLARE @colnumber int = 1
DECLARE @command VARCHAR(4000) =''

WHILE @colnumber <= 125
BEGIN
SET @command = @command + ' col' + CAST(@colnumber AS varchar(3)) + ' int, '
SET @colnumber += 1
END
SET @command = 'CREATE TABLE StarPerform (PerfId int, ' + @command + ' Lastcolumn int)'

EXEC (@command)

Now I query the table using the SQLQueryStress tool and I choose a high Number of Iterations to get a meaningful average:

In the Client Seconds/Iteration (Avg) you see that the performance difference for an individual query is negligible but a similar query often executed on a busy server can result in 15 extra seconds on 5000 Iterations.

Broken code

Using SELECT * in Views is also a bad practice because changes to the underlying table will return unexpected results or fail completely.
First I create a table and insert some data:

Code: tsql

--Create the table
CREATE TABLE StarBreak
(
ID int IDENTITY (1,1),
Name varchar (10),
DateFirstPost date,
DateLastPost date
);
GO

--Insert some values
INSERT INTO StarBreak (Name, DateFirstPost, DateLastPost)
VALUES ('Denis','20080207','20130101'),('Ted','20081107','20121231'),('Koen','20121123','20121227'),('Jes','20101210','20121221');
GO

Now I create and query a View to return all the columns:

Code: tsql

CREATE VIEW GetStarFromStarBreak
AS
SELECT * from Starbreak;

GO

And I get this result back:

Now let’s drop the table and recreate it but switch the position of the two datecolumns:

Code: tsql

--Drop the table
DROP TABLE StarBreak;
GO

--Create the table
CREATE TABLE StarBreak
(
ID int IDENTITY (1,1),
Name varchar (10),
DateLastPost date,
DateFirstPost date
);
GO

--Insert some values
INSERT INTO StarBreak (Name, DateFirstPost, DateLastPost)
VALUES ('Denis','20080207','20130101'),('Ted','20081107','20121231'),('Koen','20121123','20121227'),('Jes','20101210','20121221');
GO

When I query the table again I get the correct result:

But when I query my view again I get the following result:

You see that the column headers are in the same order as in the initial table but the data reflects the column order of the second table.
Dropping the table, and adding a column in the middle will also result in the above behavior:

Code: tsql

--Drop the table
DROP TABLE StarBreak;
GO

--Create the table
CREATE TABLE StarBreak
(
ID int IDENTITY (1,1),
Name varchar (10),
Gender char(1),
DateLastPost date,
DateFirstPost date
);
GO

--Insert some values
INSERT INTO StarBreak (Name, DateFirstPost, DateLastPost)
VALUES ('Denis','20080207','20130101'),('Ted','20081107','20121231'),('Koen','20121123','20121227'),('Jes','20101210','20121221');
GO

What happens when I recreate the table but only 3 instead of 4 columns? Let’s try:

Code: tsql

--Drop the table
DROP TABLE StarBreak;
GO

--Create the table
CREATE TABLE StarBreak
(
ID int IDENTITY (1,1),
Name varchar (10),
DateLastPost date,
);
GO

--Insert some values
INSERT INTO StarBreak (Name, DateLastPost)
VALUES ('Denis','20130101'),('Ted','20121231'),('Koen','20121227'),('Jes','20121221');
GO

Now query the view again but use this query to make sure you only select the 3 existing columns:

Code: tsql

SELECT ID, Name, DateLastPost FROM GetStarFromStarBreak

And the result is:

You see the code breaks because the view still expects 4 columns although you specified you only query 3 columns from the view.

Conclusion

We have seen several reasons to stay away from the SELECT * statement but we do understand the pain in typing in 127 column names. So here is a last tip:
When you drag the Columns folder of your table in Object Explorer to the Query Pane, SQL Server Management Studio will automatically list the column names separated with commas for you.

Original post blogged on LessThanDot.

Database Projects - Setting up Source Control

Axel Achten (axel8s) — Tue, 11 Dec 2012 12:38:00 +0000

For some time I wanted to document the possibilities of the SQL Server Database Project and how developers and DBA's can benefit from it.
Problem Statement
Many companies I've worked for struggle with their database lifecycle management:

Production databases are copied to Test, Staging, UAT... environments by the DBA's with production (sensitive) data
DBA's spend too much time doing the copy operations
Backup chains are broken because of the lack of use of the WITH COPY_ONLY option
Changes are made by the DBA executing individual scripts, sometimes breaking because the development database and production database were out of sync.
Bugs are "Emergency" fixed in the production database so the schemas of the development and production database are out of sync
...

Possible solution
In general there is not much to learn from a developer (just kidding!) but normally they have some software lifecycle management and typically use a Source Control Provider to keep version information and the production version of their software. With SQL Server Data Tools and using a Database Project we should be able to setup a similar lifecycle management for our databases. In my next series of posts I will explore the possibilities of the Database Project and share them with you.
Getting started - Choosing a Source Control Provider
Again as a DBA I did not know what Source Control Provider to choose, I worked with Visual Source Safe a decade ago and had to do a setup of Team Foundation Server once and didn't want to go through this pain again. A quick poll on twitter on which Source Control Provider to use was depressing. Probably because our American friends were sleeping, the Belgian Developers were still hitting the snooze button and Christiaan was walking his dog. Lucky for me Dave Dustin (twitter) from the other side of the world gave me some possibilities. I the next example I will use AnkhSVN, Subversion with Support for Visual Studio. You can download the setup from here.
The setup
Launch the installer, accept the license agreement and hit Install:

Off course you have to allow the program to install software:

Et voila, the setup is finished:

When you open SQL Server Data Tools and open the Options pane in the Tools menu you should see that AnkhSVN - Subversion Support for Visual Studio is selected as the Current Source Control plug in, if not you can select it from the list:

Now that we have a Source Control provider we can get started with our database project but this will be the subject of my next post.

Original post blogged on LessThanDot.

Book Review: Microsoft SQL Server 2012 T-SQL Fundamentals

Axel Achten (axel8s) — Tue, 27 Nov 2012 17:37:00 +0000

With a basis as a system engineer, I became a DBA. And DBA's don't often write real DML queries. I can read a query, understand what it's doing but when I need to read one I need BOL the help me out. Taking the 070-457 Transition Your MCTS on SQL Server to MCSA: SQL Server 2012,Part 1 exam showed me I'm strong in database administration but there was room for improvement for my query skills. That's why I bought Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan (blog | twitter).

Chapter 1: Background to T-SQL Querying and Programming
When you followed a bunch of trainings and read some books about SQL Server, you will possibly have seen some chapters on the origin and basics of relational databases and the SQL language. But I bet you have never seen a chapter like this one. In a straight and clear way Itzik guides you through the mathematics behind SQL, the flavors and architecture of SQL Server and the definitions of tables en data integrity. Now you're ready to stand between other professionals and at least know what they are talking about. For me, they should include these 25 pages in every SQL Server learning book.

Chapter 2: Single-Table Queries
In the largest chapter of this book, the writer explains every part of the SELECT statement and how these queries are processed by SQL Server. It's the basis a user needs to start understanding set-based logic. In the second part of the chapter predicates and operators, CASE expressions, NULL handling, character data, date and time data and even metadata is handled. It might seem overwhelming for starters but all the essentials for writing queries are in this chapter.

Chapter 3: Joins
I thought I knew how joins work, but after this chapter I know a lot more. All join types are explained in detail including a subchapter Beyond the Fundamentals. And again explained in such a clear way. My students will certainly benefit from this.

Chapter 4: Subqueries
The chapter starts with the self-contained subqueries, that are easy to understand and moves on to the correlated subqueries and shows the power of them. The EXISTS predicate is also well explained. In the beyond the fundamentals of the chapter some examples are shown on how to build windowing functionality without the windowing functions. The only disadvantage in the chapter is the fact that SOME, ANY and ALL are not covered because they are rarely used. From experience I know they might show up on Microsoft exams.

Chapter 5: Table Expressions
A beautiful chapter where you will learn to write real powerful queries using derived tables, Common Table Expressions, Views , Functions and the APPLY operator. You'll see the true value of these expressions when you see the complex definitions of the exercises and the simple queries you need in the solution.

Chapter 6: Set Operators
UNION, INTERSECT, EXCEPT will have no more secrets for you, the writer even shows you how to create queries to implement the INTERSECT and EXCEPT ALL operators that are supported in Standard SQL but are not implemented in T-SQL.

Chapter 7: Beyond the Fundamentals of Querying
Everybody has heard of the new Windowing functions in SQL 2012 but little people can explain it as clear and simple as it's written down in this book. Also pivoting is explained again and all the possibilities of groupings and grouping sets are written out in plain T-SQL and with use of the functions so you can really see what the different key words are doing. So the chapter goes indeed beyond the fundamentals, but I would mark it as one of the essentials you should learn about T-SQL

Chapter 8: Data Modification
The basics of INSERT, UPDATE, DELETE and MERGE, BULK INSERT, Identities and Sequences all well explained. But then UPDATE based on a JOIN, DELETE with TOP and OFFSET-FETCH and off course the OUTPUT clause with all its possibilities. I think a lot of production code will be optimized after reading this chapter.

Chapter 9: Transactions and Concurrency
The chapter starts with the theory of transactions, locks, blocks and some copy and paste code to start troubleshooting blocking issues on your production servers. But the decent explanation of how the Isolation Levels work with simple examples makes it understandable for everyone.

Chapter 10: Programmable Objects
The last chapter in the book is about variables, batches, cursors, procedures,etc. ... And because they are not considered as fundamentals you get an overview with decent examples to get you started.

Conclusion
If you need to write queries with T-SQL, this is a book that will get you started. After every chapter you'll find also a number of exercises to test what you have learned.

For me the book is decent value for money, now let's see when I can schedule my exam.

Original post blogged on LessThanDot.

Why you should register for SQL Server Days 2012

Axel Achten (axel8s) — Tue, 09 Oct 2012 11:54:00 +0000

On 19 and 20 November, the annual SQL Server Days will take place again. This 2 day event organized by the Belgian SQL Server User Group is the event for all people working with MS SQL Server in Belgium (others are welcome too). From beginner to expert, from developer to DBA, from Excel specialist to BI designer.
Why?
Because they provide a different track for all the profiles even for the Oracle DBA's.
Because some international top speakers are coming like :

Bob Beauchemin (Blog | Twitter): Whenever you see someone from SQLskills on a speaker list you shouldn't doubt and just go. I've seen Bob teaching in Barcelona twice and can assure you, listing to him is fun but be prepared for some deep learning
Dandy Weyn (Blog | https://twitter.com/ilikesql): The Belgian who presented the American MS SQL Server 2012 roadshow. Who wants to miss that?
Mladen Prajdic (Blog | Twitter): When you're able to write a SSMS plugin like the SSMS Tools Pack, you can tell a lot more about the tricks nobody heard of.

Because also Belgian top speakers will present some sessions :

Karel Coenye (twitter) will be talking about the forgotten fill factor
Pieter Vanhove (blog | twitter)will help you set your backup settings right
Rising SSIS star Koen Verbeeck (twitter) will celebrate the wedding between SSIS and CDC
O-Advisor Wesley Backelant (twitter)will finally tell you what this OData thing is you keep hearing about.

Because you just want to meet over 400 peers from Belgium who work with MS SQL Server.
So what are you waiting for?

Original post blogged on LessThanDot.

T-SQL CASE functions

Axel Achten (axel8s) — Mon, 03 Sep 2012 10:12:00 +0000

With the CASE statement you can add conditional logic to your T-SQL code. T-SQL 2012 has certain functions that can be seen as CASE shortcuts. With these functions you can quickly use some CASE functionality and as a surplus, because they are functions you can use them everywhere where expressions are allowed. The examples are written against the AdventureWorks2012 database:

ISNULL

The ISNULL('Null_Expression','Replace_Value') function evaluates the value of the 'Null_Expression' and if the result is NULL it returns the 'Replace_Value' value. Otherwise the 'Null_Expression' value is returned:

Code: tsql

SELECT TOP 10 ISNULL(MiddleName,'NA')AS NoNullMiddleName FROM Person.Person

In the result we see that all the NULL values are replaced by the 'NA' value. Values that were not NULL keep their value:

COALESCE

This function accepts 2 or more arguments and will return the first non NULL value. In the next example I use a common table expression and fill it with items with a weight and no size, items with a size and no weight and items with a size and weight:

Code: tsql

WITH Product_CTE (name, size, weight) AS
(
SELECT TOP 10 name, size, weight FROM Production.Product
WHERE size IS NOT NULL AND weight IS NULL
UNION
SELECT TOP 10 name, size, weight FROM Production.Product
WHERE weight IS NOT NULL AND size IS NULL
UNION
SELECT TOP 10 name, size, weight FROM Production.Product
WHERE weight IS NOT NULL AND size IS NOT NULL
)
Select name, size, weight, COALESCE(size,CAST(weight AS VARCHAR(10))) AS coalesceresult
FROM Product_CTE

In the result you see that the size is shown in the coalesceresult column unless the original value was NULL. In that case the weight value is returned:

IIF

Just like in SSIS and MS Access, SQL Server 2012 now supports the IIF('logical_expression','expression_if_true','expression_if_false') function. So if the 'logical_expression evaluates to TRUE the 'expression_if_true' value is shown, otherwise the 'expression_if_false' is executed:

Code: tsql

SELECT IIF(Title LiKE '%s.','Miss','Mister') AS Title, FirstName
FROM Person.Person
WHERE Title IS NOT NULL

Since IIF supports the true/false expressions you can even put logical expressions in their place to create single line CASE scenarios to have more possibilities. For ease of use and general readability of your code, I would not recommend this. The result of the previous query:

CHOOSE

CHOOSE('index','expr1','expr2',...,'exprn') is also known in MS Access and new in SQL Server 2012 and gives the possibility to use an index number and the result of the corresponding index expression:

Code: tsql

SELECT FirstName, LastName, MiddleName, CHOOSE(2,FirstName, LastName, MiddleName) AS Chosen
FROM Person.Person

In normal coding you would dynamically assign a value to the index with a variable. The result of the above query is:

Conclusion

The CASE shortcuts can be very useful in coding scenarios but note that only COALESCE is ANSI standard. IIF and CHOOSE are added to the T-SQL 2012 language to ease the migration from MS Access to SQL Server.

Original post blogged on LessThanDot.