A blog about SQL Server, Books, Movies and life in general
Monday, January 16, 2012
OT: Being healthier as a geek
Sunday, December 11, 2011
SQL Advent 2011 series of posts
Day 2: System tables and catalog views
Day 3: Partitioning
Day 4: Schemas
Day 5: Common Table Expressions
Day 6: Windowing functions
Day 7: Crosstab with PIVOT
Day 8: UNPIVOT
Day 9: Dynamic TOP
Day 10: Upsert by using the Merge statement
Day 11: DML statements with the OUTPUT clause
Friday, July 15, 2011
6 new SQL Server Denali CTP3 posts
These posts are about the new things introduced in SQL Server Denali CTP3, below is a list of the posts
A Quick look at the new IIF function in Denali CTP3
Tuesday, November 09, 2010
Post I created about SQL Server Denali
I have been playing around with Denali for a couple of weeks now and created a couple of posts. Below is a list of the posts, click on each link to get some more information about Denali
First look at SQL Server Management Studio Denali
Screenshots of the new look and feel of BIDS in Denali
Find what is deprecated in SQL Server Denali by using sys.dm_os_performance_counters
Playing around with sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object
A first look at sequences in SQL Server Denali
Don’t install Denali BIDS side by side with a 2008 instance
The new Dynamic Management Views in SQL Server Denali
Debugging In SQL Server Denali
Using OFFSET N ROWS FETCH NEXT N ROWS ONLY In SQL Server Denali for easy paging
Tuesday, October 13, 2009
Win A Copy Of 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
Monday, September 28, 2009
10 must read SQL Server posts by Ted Krueger
6 ways to import data into SQL Server
Recover a database without the ldf file
Dynamic column names and fields in SSRS (Custom Matrix)
Rebuild master and restore system databases from complete disk failure
Do not truncate your ldf files!
Proactive Deadlock Notifications
Troubleshooting Blocking 101 on SQL Server
Not a fan of the Report Manager in SSRS? Using SSRS procedures to get the job done
Dynamic Data Sources in SSRS
Quick Data Model creation off SSAS Data Source in SSRS
You can also follow Ted to see what is up to on twitter, his username is @onpnt
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
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
- Finding Out How Many Times A Table Is Being Used In Ad Hoc Or Procedure Calls In SQL Server 2005 And 2008
- When Books On Line is not really correct
- Dealing with the could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT error message
- The differences between LEN and DATALENGTH in SQL Server
- Ecofont, Will this save the planet?
- Operand type clash: date is incompatible with int error when trying to do +1 on a date data type in SQL Server 2008
- A couple of ways of getting the top 2 distinct values from a set in SQL Server
- Vulnerability in Microsoft Video ActiveX control could allow remote code execution
- How to copy data/append data into files from within T-SQL
- How To Script Out The Whole Database In SQL Server 2005 and SQL Server 2008
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
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
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.
Thursday, October 16, 2008
today is a sad day
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
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
Thursday, September 18, 2008
Interview With Louis Davidson Author of Pro SQL Server 2008 Relational Database Design and Implementation
Interview With Louis Davidson Author of Pro SQL Server 2008 Relational Database Design and Implementation
Monday, August 25, 2008
Interesting Bug/Feature In SQL Server 2008 RTM
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
SELECT * FROM
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
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.
Monday, August 11, 2008
Microsoft Visual Studio 2008 Service Pack 1 Available For Download
Here is the link to the 831MB ISO
http://www.microsoft.com/downloads/details.aspx?FamilyId=27673C47-B3B5-4C67-BD99-84E525B5CE61&displaylang=en
Finally, Now I can also install SQL Server 2008 :-)
Wednesday, August 06, 2008
SQL Server 2008 is RTM
Sunday, July 20, 2008
Microsoft Visual Studio Team System 2008 Database Edition GDR July CTP Released
In addition to providing support for SQL Server 2008 database projects, this release incorporates many previously released Power Tools as well as several new features. The new features include distinct Build and Deploy phases, Static Code Analysis and improved integration with SQL CLR projects.
Database Edition no longer requires a Design Database. Therefore, it is no longer necessary to install an instance of SQL Express or SQL Server prior to using Database Edition.
Download it here: http://www.microsoft.com/downloads/deta ... laylang=en
Monday, July 07, 2008
sp_indexinfo an enhanced index information procedure
This stored procedure will tell you the following"
What indexes exists for a or each table(s)
Clustered, non-clustered or heap
Columns in the index
Included columns in the index
Unique or nonunique
Number rows in the table
Space usage
How frequently the indexes has been used
Check it out here: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp
Wednesday, July 02, 2008
Awesome Collection Of ASP.NET Hacks, Tips and Tricks
we have the following categories
1 Applications
2 Caching
3 Controls
4 Database
5 Dates
6 Debugging
7 Email
8 Encryption
9 Files
10 Images
11 Javascript
12 Objects and Classes
13 Pages
14 Sessions
15 Strings
16 Validation
17 Visual Studio
18 Web
The URL to these hacks is here: http://wiki.lessthandot.com/index.php/ASP.NET_Hacks
Bookmark that URL because we will be adding more hacks, tips and tricks