May 2007 Blog Posts
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.
So lets see what each chapter covers:
Chapter 1: DataType Related problems, XML and CLR UDTs
Talks about DateTime problems, patterns matching , conversions, large objects, CLR and of course XML which is the biggest part of the chapter.
Chapter 2: Temporary tables and Table variables
A great explanation of how, why and what in the world of Temp.
Chapter 3: Cursors
Shows that although dreaded by many, they have their rightfull...
Now this is something i really didn't think it would compile in C#.
private int _someVar = 0;
private void DoStuff1()
{
int _someVar = 0;
_someVar = 6;
// ... code that uses _someVar
}
private void DoStuff2()
{
_someVar = 5;
// ... code that uses _someVar
}
We should at least get a warning if you ask me...
One learns something new every day.
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.Split
GO
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
INSERT INTO @t(data)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
...
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.
More here
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.v1 to insert data into table')
END
GO
CREATE VIEW v1
WITH SCHEMABINDING
AS
SELECT id, title
FROM dbo.t1
...
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:
Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
Temp tables behave same as normal tables and are bound by transactions.
A simple example shows this difference quite nicely:
BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2,...
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.
Just basic stuff.
Chapter 2 covers the phyisical query processing. Explaines what parsing, algebratization and executions plans are and how it's all tied together.
Chapter 3 covers the query tuning methodology and tools to use for it. In my opinion This chapter is one of the best in the book.
Chapter 4 introduces us to subqueries, CTE's and Rank...
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.
If you're a newbie who is just getting it's feet wet then first get some experience and comfortability with SQL server first.
The book is a very easy read and although it has some errors they're not huge so i didn't need errata (fixed errors) for it.
It nicly summarizes the topics that you need to know about and the chapters are just long enough.
Content fits very nicly with...
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.
So you have to be comfortable with T-SQL syntax and Management Studio alike.
Knowing what all of the little buttons and options do helps :)
The tools i used for learning are SQL Server Books Online and
EXAM CRAM: MCTS 70-431: Implementing and Maintaining Microsoft® SQL Server 2005
I've read some more books but they were really not needed to pass this exam.
I'll post my reviews...