Wednesday, December 26, 2012

SQL Advent 2012: Recap

Another year, another SQL Advent calendar has come to an end. This year there was not much SQL code after day 10, I still hope you found it interesting and useful. Here is a list off all the posts with a little blurb about them.

SQL Advent 2012 Day 24: Getting help
In this post I showed you where to get help and how to use twitter to get some help

SQL Advent 2012 Day 23: Get the tools you need to be more productive
In this post I tried to show you that tools will pay for themselves

SQL Advent 2012 Day 22: Testing your backup and failover strategy
This post was all about the importance of testing your backup and failover strategy

SQL Advent 2012 Day 21: With VLDBs it matters what you do and how you do it
With little databases you can get away with doing stupid stuff, with large databases not so much

SQL Advent 2012 Day 20: Use the new features
This post detailed why it was important to use the features and functionality

SQL Advent 2012 Day 19: How to improve your tech skills
The post showed you a couple of ways to improve your tech skills

SQL Advent 2012 Day 18: Stay relevant and marketable
If you don't keep up, you will lose

SQL Advent 2012 Day 17: No matter how long you are on the wrong path, go back
Sometimes it is better to start from scratch than continue down the wrong path

SQL Advent 2012 Day 16: Lack of database design and normalization
We don't need no stinking keys

SQL Advent 2012 Day 15: Benefits of Indexes
Why do we need indexes and what purpose do they serve?

SQL Advent 2012 Day 14: When to say no
Sometines you have to say no....even to your boss

SQL Advent 2012 Day 13: Features enabled that are not used
Disable and shutdown services and feature that are not used

SQL Advent 2012 Day 12: Proactive notifications
Get notified before the client or your boss about errors

SQL Advent 2012 Day 11: Cursors and loops
Cursors are almost always evil

SQL Advent 2012 Day 10: SQL Server Maintenance
Maintenance is unfortunately required, machines and software are not completely autotuned yet

SQL Advent 2012 Day 9: Reinventing the wheel
Why write something if it already exist

SQL Advent 2012 Day 8: Foreign Keys
A small post showing that you don't always need a primary key for a foreign key

SQL Advent 2012 Day 7: Lack of constraints
Constraints in a database guarantee data integrity

SQL Advent 2012 Day 6: Standardized Naming And Other Conventions
If you have a standard, everything will be easier

SQL Advent 2012 Day 5: Do not trust the SSMS designers
SOmetimes a simple click can cause a lot of data to be moved behind the scenes

SQL Advent 2012 Day 4: Triggers, what to do, what not to do
Some best practices around triggers

SQL Advent 2012 Day 3: Sargable Queries
How to write queries that will take advantage of indexes

SQL Advent 2012 Day 2: Data types storage differences
Make sure you know the difference between the different data types and how they are stored

SQL Advent 2012 Day 1: Sizing database files
You have to presize your databases if you want optimal performance

Wednesday, December 05, 2012

SQL Advent 2012 has started

Just like last year, I decided to do a SQL Advent this year.
There are 4 posts up so far
Data types storage differences
Sargable queries
Triggers, what to do, what not to do
Sizing Files
Don’t trust the designers

In case you are interested in last year's posts, all of them are listed here: SQL Advent 2011 Recap

Monday, January 16, 2012

SQL Advent Recap and What is coming in SQL Server 2012 recap

In case you missed these, here is a recap of the SQL Advent and the SQL Server 2012 posts

SQL Advent Recap

Day 1: Date and time
In this post I covered the new date, datetime2 and time datatypes
Day 2: System tables and catalog views
In this post we took a look what the replacements are for the all system tables and also gave you a table with the new catalog view/compatibility view equivalent of the old system table
Day 3: Partitioning
In this post I looked at partitioning in pre sql 2005 days by showing you how to create partitioned views. I also showed you how to user partitioned function in sql 2005 and up
Day 4: Schemas
In this post I show you what schemas are and how they can help with security and logical grouping of objects
Day 5: Common Table Expressions
The Common Table Expressions post showed you what Common Table Expressions are and how they can be used to simplify your code
Day 6: Windowing functions
The Windowing functions post showed you how to do different kinds of rankings
Day 7: Crosstab with PIVOT
This post was all about pivoting/transposing/crosstabbing data with the PIVOT operator, also was shown how to do it dynamically
This post showed you how to use UNPIVOT to get the reversed effect of PIVOT
Day 9: Dynamic TOP
The dynamic TOP post showed you how to do dynamic TOP without dynamic SQL or SET ROWCOUNT
Day 10: Upsert by using the Merge statement
This post was all about how to use MERGE to do an UPSERT (Update if it exists otherwise insert)
Day 11: DML statements with the OUTPUT clause
This post showed the usefulness of the OUTPUT clause
Day 12: Table Value Constructor
This post showed you how to use Table Value Constructor
Day 13: DDL Triggers
The DDL trigger post showed you how to use DDL triggers and also explained why you might want to use them
Day 14: EXCEPT and INTERSECT SET Operations
This post was all about the two new SET Operations EXCEPT and INTERSECT 
Day 15: Joins
This post showed you how to use the newer ANSI SQL JOIN syntax and also showed you what was deprecated
Shown was how to use APPLY with derived tables as well as functions
Day 17: varchar(max)
In this post I showed you why varchar(max) is much better than the text data type
Day 18: Table-valued Parameters
I showed you how to use Table-valued Parameters to pass around tables
Day 19: Filtered Indexes
In this post I showed you how to create a filtered index and why it can be beneficial in your database
Day 20: Indexes with Included Columns
On this day I showed you how to cover you query by using Indexes with Included Columns
Error handling go better in SQL Server 2005 and I show you how to use TRY CATCH
Day 22: Dynamic Management Views
In this post I show how you can use Dynamic Management Views to get all kinds of information about your server and databases
The OBJECT_DEFINITION covers ways to get the text of an object and also show you why it is better than sp_helptext or syscomments
This post is all about rebuilding and reorganizing(defragmenting) indexes

What is coming in SQL Server 2012
First look at SQL Server Management Studio Denali
In this post I show you what the new SQL Server Management Studio which is based on Visual Studio 2010 looks like
A first look at sequences in SQL Server Denali
Sequences finally made it into SQL Server, this post will show you how to use sequences
Screenshots of the new look and feel of BIDS in Denali
Business Intelligence Development Studio has a new look and feel, SSIS has never looked better
Debugging In SQL Server Denali
Debugging got much better in SQL Server 2012, this post will look at some of the new things
Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging
With OFFSET N ROWS FETCH NEXT N ROWS ONLY paging has never been easier
Playing around with sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object
The sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object data management views makes it easy to find out what a proc or SQL statement returns
A Quick look at the new IIF function in Denali CTP3
SQL Server now has the IIF statement, this is just shorthand for CASE but it will make for shorter code
A Quick look at the new EOMONTH function in SQL Server Denali CTP3
The EOMONTH function will give you the last date of the month
With DATEFROMPARTS and DATETIMEFROMPARTS you can construct a date by passing in a bunch of integers
ColumnStore Index limitations in SQL Server Denali CTP3
Some limitation that the current version of ColumnStore Index have
Concat function in SQL Server Denali CTP3
Finally SQL Server 2012 has the Concat, this is something other RDBMSes had for years
Format function in SQL Server Denali CTP3
Formatting has never been easier, no need to use style parameters anymore
SQL Server Columnstore Index FAQ wiki page published
Columnar storage is new in SQL Server 2012, make sure to read the wiki for all your questions
TRY_CONVERT in SQL Server Denali CTP3
Before converting, use TRY_CONVERT that will tell you if the value can be converted to the data type you want
SQL Server Code Name “Denali” CTP3 Product Guide available for download
There is some cool documentation available, this includes white papers and videos

OT: Being healthier as a geek

This is not a technical post but I decided to spend a little of my blogging and web surfing time on my body this year...oh how vain right? Of course I didn't start January 1st but on January 8th, why do the same as everyone else. I lost 4.8 pounds the first week, you can follow my progress here: