This book is a sequel to T-SQL Querying. And it stands completly side by side it.
I can only recommend that you get it. The amount of new stuff to learn is amazing.
Read more →
Here's a Split function using XML datatype.
It's preety neat and simple compared to all others that i've seen.
Forget While Loops and recursive CTE's. Enter XML: IF OBJECT_ID('dbo.Split') IS NOT NULL DROP FUNCTION dbo.
Read more →
There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.
This is simply not true. To see why not, let's review what each statement does.
Read more →
Ever wanted to have have a table that contains unique values but needs to have multiple null values also?
Here's how to do it:
SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO CREATE TABLE t1 (id INT, title VARCHAR(20)) GO – optional instead of trigger to disable the insert directly into the table CREATE TRIGGER trg_t1_DisableInsert ON t1 INSTEAD OF INSERT AS BEGIN – use 18 to stop further processing RAISERROR (40000, 18, 1, 'Use view dbo.
Read more →
We've all heard about differences between temporary tables and table variables in SQL Server.
They include performance, storage in memory or disk, tempdb use, etc.
But the biggest and mostly overlooked difference is:
Read more →
This is an AWSOME book!
Written by Itzik Ben-Gan and coauthored by Lubor Kolar and Dejan Sarka it's definitly worth it's money.
It starts with the chapter on logical query processing in which it explaines the basics of the full select statement and the order of processing it.
Read more →
Well in my knowledge brush up for 70-431 Exam I used this book backed up by BOL.
If you have previous SQL Server experience this book and BOL is all you really need to pass the Exam.
Read more →
So as of today i'm a proud member of the MCP club :)
The exam wasn't that hard. I had 35 "Pick a correct answer" questions and 15 simulations.
The simulations threw me off a bit because i didn't expect them.
Read more →
This has been a debate for as long as i've known. How to pronounce SQL? ess-cue-el or sequel?
Each one has it's own supporters. But only one is correct.
And the winner is (drum-roll here):
Read more →
There's no such thing as a global variable in SQL Server.
You can't just do: DECLARE @@GlobalVar int
You can fake it with CONTEXT_INFO
but i wanted something that would last beyond a session or restart
Read more →
The most popular method of auditing data is with an AFTER DELETE, INSERT, UPDATE trigger.
But what happens if you have a Rollback statement in your trigger if some condition is met?
Read more →
I've written an article about Multiple Active Result Sets (MARS) and it's published on SQLTeam.com
Multiple Active Result Sets is a new SQL Server 2005 feature that, putting it simply, allows the user to run more than one SQL batch on an open connection at the same time.
Read more →
We had an interesting discussion here about Katmai (the next SQL Server version)
Of course it was discussed in the latest MVP summit, but noone can say anything because it's all under NDA.
Read more →
I just hate having nulls in my DateTime columns. Having them always mean you also have to handle them in some way in your app.
The most common way is something like this:
Read more →
MsSqlSystemResource is a database that complements the master db. It is like the name smartly imples a resource database.
All system stored procedures, views and functions are stored here.
This databse is by all means hidden from the user.
Read more →
While searching for something in BOL i've accidently stumbled onto this little cool command line utility
TableDiff.exe is a table comparison tool that comes with the sql server.
It's installed on the server in the:
Read more →
Since i've started playing with SQL Server 2005 i've come accross a few things that i think are very cool.
I write them here so i don't forget about them :))
Read more →
When doing apps that deals with date there almost always comes a question on how to store an incomplete date.
For example:
Person A is born on 1980-02-17.
Person B is born on 1980-02 <- The person doesn't know the exact day (This is acctually a real life scenario)
Read more →
I've seen this question pop up here, and i think it's usefull to post it here, since not every one reads the forums (you should! :))
We'll in this thread at the end of the first page there's a procedure on how to downgrade a db from SQL Server 2k5 to SQL server 2000.
Read more →
Ever missed a Prod(columnName) function that works like a sum but it multiplies the column values?
If you have then you probably know that there's a workaround using a bit of high school math knowledge about base 10 logarithms.
Read more →