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
Day 8: UNPIVOT
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
Day 16: CROSS APPLY and OUTER APPLY
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
Day 21: TRY CATCH
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
Day 23: OBJECT_DEFINITION
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
Day 24: Index REBUILD and REORGANIZE
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
DATEFROMPARTS and DATETIMEFROMPARTS functions in SQL Server Denali CTP3
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: http://188point6.blogspot.com/

Sunday, December 11, 2011

SQL Advent 2011 series of posts

I have started a series of post about upgrading your skills to 2005 and 2008 versions of your code from 2000. Below is a list of posts that are already up


Also check out the post Are you ready for SQL Server 2012 or are you still partying like it is 1999? which is the one that explains why I did this

Tuesday, October 13, 2009

Win A Copy Of SQL Server 2008 Administration in Action

Rod Colledge was kind to give the readers of Less Than Dot a chance to win a pdf copy of his book SQL Server 2008 Administration in Action.

Here is how you can win this book, first read the Interview With Rod Colledge About The Book SQL Server 2008 Administration in Action post and tell me which question or answer you liked the most and why. Leave a comment on that post, do not leave it here. I will announce a winner this Friday at 12 PM EST here: Interview With Rod Colledge About The Book SQL Server 2008 Administration in Action

Wednesday, August 05, 2009

Some SQL Server posts I wrote lately and I now have a RSS feed just for my posts on lessthandot.com

The reason I am writing this post is that we now have RSS feeds on lessthandot for a specific author. In my case the RSS feed is the following: http://blogs.lessthandot.com/index.php/All/?tempskin=_rss2&author=4

This URL has all the blogposts I have written: http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=4


Here is a list of the last 10 posts I wrote about SQL Server



And remember, if you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

Monday, November 24, 2008

With this post I bid you all adieu

I have posted on this blog for a little over 3 years now and decided it is time to call it quits at this URL.
All SQL Server related post from me in the future will be on SQLBlog.com and on lessthandot.com.
All non tech post will be on denisgobo.blogspot.com, here you will also be able to connect with me on twitter, flickr, friendfeed and linkedin


below are some URLs

Lessthandot.com
Mainsite
Database Blogs
Database Blogs RSS feeds
All Blogs
All Blogs RSS Feed

SQLBlog.com
Mainsite
My Blog
My Blog RSS feed

Personal Blog
URL
RSS Feed

See you there, don't forget to subsrcibe

Friday, October 31, 2008

SQL Server 2005 Service Pack 3 Beta and SQL Server 2008 Feature Pack October 2008 available for download

SQL Server 2005 Service Pack 3 Beta and SQL Server 2008 Feature Pack October 2008 are available for download

Download SQL Server 2005 Service Pack 3 - Beta here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D22317E1-BC64-4936-A14B-7A632B50A4CA&displaylang=en

To obtain SQL Server 2005 SP3 Beta for Express Edition or Express Edition with Advanced Services go here: http://www.microsoft.com/downloads/details.aspx?FamilyId=80DFF99C-CC82-4639-92BB-BBDAFF677792&displaylang=en



Microsoft SQL Server 2008 Feature Pack October 2008 is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:

  • Redistributable components for SQL Server 2008.
  • Add-on providers for SQL Server 2008.
  • Backward compatibility components for SQL Server 2008.
Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en

Thursday, October 16, 2008

today is a sad day

This was not the way I envisioned going back to Amsterdam. My mom past away today, two weeks shy of her 60th birthday
Since my passport was expired they issued me a temporary passport. My parents were visiting me in Princeton last month so I did see my mom recently. I just feel sad for my dad now who is all alone. :-(

Friday, October 03, 2008

Help me win this bet

You might know that some friends and myself have launched a site named LessThanDot, this is a site where users can collaborate via the wiki, ask questions in the fora and can read out blogs. It is a technical site but we do have a forum where you can post pretty much anything you want(not vulgar or offensive). Here are some examples
The Rage of the Previously Rich 
We're In Ur Bank, Bailing It Out 
Girls Habanero Eating Contest 

Of course we do have our technical forums, some examples
Microsoft SQL Server
ASP.NET
Tech Rants

So I made a bet with the other owners of this site that I will have the most people sign up until November 1st. So please sign up and let your coworkers/friends know and let them sign up too, after all you don't want me to lose or do you?


Don't be scared to participate in the forums  :-)



My username is SQLDenis

Monday, August 25, 2008

Interesting Bug/Feature In SQL Server 2008 RTM

Someone had a problem with 8 year old procs which started to fail after moving to SQL Server 2008
Of course he should have used ints, but let's see what happens

Run this code on SQL Server 2005 and 2000

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed


IF (@num_Passed = 0)
PRINT 'True';



No problem right?

Run just this part on SQL 2008


DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;

SELECT @num_Passed


No problem either
Now run this whole thing

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = -1;
SELECT @num_Passed


IF (@num_Passed = 0)
PRINT 'True';




Oops, this is what we get
Server: Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.


Change the -1 to 1

DECLARE @num_Passed Numeric(2, 0);
SET @num_Passed = 1;
SELECT @num_Passed



IF (@num_Passed = 0)
PRINT 'True';




No problem either.

Run this

IF (convert(Numeric(2, 0),-1) = 0)
PRINT 'True';

That fails
Let's make it numeric(3,0)

IF (convert(Numeric(3, 0),-1) = 0)
PRINT 'True';



No problem, that runs fine. So is this a bug because of implicit conversion to tinyint which can't hold negative values?

Sunday, August 17, 2008

Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

Take a look at this query.

SELECT * FROM

  • (

  • SELECT customer_id, ‘MTD’ AS record_type, COUNT(*), SUM(), AVG()

  • FROM payment_table


  • WHERE YEAR(payment_dt) = YEAR(GETDATE())

  • and MONTH(payment_dt) = MONTH(GETDATE())

  • GROUP BY customer_id) MTD_payments

  • UNION ALL

  • (

  • SELECT customer_id, ‘YTD’ AS record_type, COUNT(*), SUM(), AVG()

  • FROM payment_table

  • WHERE

  • WHERE YEAR(payment_dt) = YEAR(GETDATE())

  • GROUP BY customer_id) YTD_payments

  • UNION ALL

  • (

  • SELECT customer_id, ‘LTD’ AS record_type, COUNT(*), SUM(), AVG()

  • FROM payment_table) LTD_payments

  • ) payments_report

  • ORDER BY customer_id, record_type



  • Can you see the problem?
    A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don't think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

    The problem is that the following piece of code

    where year(payment_dt) = year(getDate())
    and month(payment_dt) = month(getDate())

    is not sargable. First what does it mean to be sargable? A query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

    This query is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. The difference between an index seek and an index scan can be explained like this: when searching for something in a book, you go to the index in the back find the page number and go to the page, that is an index seek. When looking for something in a book you go from page one until the last page, read all the words on all the ages and get what you need, that was an index scan. Do you see how much more expensive in terms of performance that was?

    Let's get back to the query, what can we do to make this piece of code use an index seek?
    where year(payment_dt) = year(getDate())
    and month(payment_dt) = month(getDate())

    You would change it to this:
    where payment_dt >= dateadd(mm, datediff(mm, 0, getdate())+0, 0)
    and payment_dt < dateadd(mm, datediff(mm, 0, getdate())+1, 0)

    You can see the complete question on the MSDN forum site here:
    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3746751&SiteID=1
    The Person said that his query went from over 24 hours to 36 seconds. Wow!! That is very significant. hardware cannot help you out if you have bad queries like that.

    The same exact day I answered a very similar question, take a look here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3752248&SiteID=1

    The person had this

    AND DATEDIFF(d, '08/10/2008', DateCreated) >= 0

    AND DATEDIFF(d, DateCreated, '08/15/2008') >= 0


    I told him to change it to this

    AND DateCreated >= '08/10/2008'
    and DateCreated <= '08/15/2008'


    And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links

    Case Sensitive Search
    No Functions on Left Side of Operator
    Query Optimizations With Dates
    Optimization: Set Nocount On
    No Math In Where Clause
    Don't Use (select *), but List Columns


    If you are interested in some blogposts about dates, take a look at these two which I wrote earlier
    How Are Dates Stored In SQL Server?
    Do You Know How Between Works With Dates?

    Wednesday, August 13, 2008

    SQL Tip, Compiling Your SQL Without Running It to See If It Would Run

    Let's say you have a big SQL script with a ton of code and you want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc etc.
    Take this simple example

    SELECT GETDATE()
    GO
    SELECT 1/asasasas
    GO


    You can probably guess that the second statement is not valid, when you have a lot of code it is more difficult to spot these things.
    Execeute the code above and you will get this


    (1 row(s) affected)

    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'asasasas'.

    SQL server has the SET NOEXEC statement. From BOL:
    When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of
    Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all
    batches are executed after compilation.

    The execution of statements in
    SQL Server consists of two phases: compilation and execution. This setting is
    useful for having SQL Server validate the syntax and object names in
    Transact-SQL code when executing. It is also useful for debugging statements
    that would usually be part of a larger batch of statements.

    The setting
    of SET NOEXEC is set at execute or run time and not at parse time.


    So execute the code below

    SET NOEXEC ON
    GO

    SELECT GETDATE()
    GO
    SELECT 1/asasasas
    GO
    SET NOEXEC OFF
    GO



    As you can see the output is the following:
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'asasasas'.


    You never see the getdate. Parsing that code will not throw an error and because of deferred name resolution you can fat-finger table names and it will parse without a problem.

    Wednesday, August 06, 2008