Pass Summit 2014 pre-conference day 2

I went to the SQL Server PASS Summit 2014 in Seattle, this is just my recap of my first day. My first day was day 2 of the pre-conference. My day started early, really early, the jet lag is killing me and I am up by 2 AM tossing and turning. By 5 I was already done with my workout, read 30 pages of the book Neuromancer and had showered.



I met a co-worker and we walked to the Seattle conference center so that he could register and we could get some breakfast before the pre-conference sessions kicked off.. You can see a picture I took of all the people eating breakfast above

I attended the Troubleshoot Customer Performance Problems Like a Microsoft Engineer, this session was presented by Tim Chapman and Denzil Ribeiro, both of them work for Microsoft CSS

The session started at 8:30 AM and ended at 4:30, I took lots of notes and I have pasted them below as is, there are typos and some stuff might not make sense to you, but these are for me, and I will use these note to investigate and research the stuff I learned at this session. The reason I took all these notes is because the pre-conference sessions are not recorded and won't be on the PASS 2014 DVDs.
Scroll down to after the notes for more stuff about this day if you don't care about these notes
Diag manager 
Choose 64 bit (AMD button)
Perfmon information..overhead is very low
Trace is collected by default... uncheck that... If you don't fin the info you want, you can go back and then run a trace as well

My_Colleectors..click on details an then enhance by picking what you need......
pssdiag.XML..change version to 12 in 3 different places  change 10.5 (2008 R2) to 11 (SQL Server 2012)  or 12 (SQL Server 2014)
Overhead is 1 to 3 percent if you are not running a trace. DOn't put the data collection files on the same drives as you SQL Server data files
SQL Nexus
-------
Modify rowset file if you want to add your own or if you want to supply columns names...SQL Nexus will figure out names by default... name  =  table name..identifies is th print statement in the collection files

WHERE %%LOCKRES%%  = '(HashNumberHere)'
Windows OS = preemptive, SQL Server OS is non preemtive

----  After break
High signal wait time =  CPU pressure
Buffer cache hit ratio is since restarted...if you have a problem in the last week but your server has been up for 6 months you might still be at 95% or higher thus masking the problem
sys.dm_io_virtual_file_stats.....stalls is what SQL Server sees, not what WIndows sees
Cost threshold of parallelism.....  default = 5 ... For OLTP you might want to set it to maybe 30... better to use MAXDOP instead of Cost threshold of parallelism.. Version of SQL Server and NUMA plays a role..hardware changes significantly since they tested this last.

COmmon High stats
WRITELOG
When database changed are flushed to the transaction log file
CXPACKET
parralel queries are happening, not a good or bad thing, sometimes less desirable for OLTP
SOS_SCHEDULER_YIELD
When a thread voluntarily releases its hold on the scheduler to allow another thread to do its work. Not necessarily a problem unless it consumes a very high % of wait time on the system
PAGEIOLATCH_*
Latching a buffer structure to move a page to disk from memory, long waits may indicate a disk or memory issue
PAGELATCH_*
A task is waiting for a page latch not associated with an IO request, can be caused by inserts into the same page or contention on allocation pages
ASYNC_NETWORK_IO
Typically occurs because the client requestion data from SQL Server is not processing the request fast enough
OLEDB
Occurs when SQL Server calls the OLE DB provider. Often associated with 3rd paty tools that heavily call DMVs. Also can be caused by RPC, linked server calls, OpenQuery, OpenRowset or profiler
LCK_*
Waiting to acquire a lock
These are accumulated after the lock has been released

RESOURCE_SEMAPHOR
Waiting for memory grant due to a high number of concurrent queries or excessive memory grant requests. Not uncomment for data warehouse workloads
LATCH_*
When a latch is acquired on some non-buffer construct. Mostly internal uses - usually not a lot you can do about it
CMEMTHREAD
When a task is waiting for a thread-safe memory object, increases when multiple tasks try to allocate from the same memory object
tempdb
Can become a bottleneck if not properly sized/allocated. Faster drives are better
Tempdb is used a lot, for example
Temporary tables and table variables
Internal work tables (spools)
Spills (hash/sort/exchange)
Version Store
Make sure all files are equally sized upon creation
For # of files we recommend
<8 Cores = 8 tempdb files
>= cores =  use 8 unless you still have latch contention, then add 4 at a time afterwards

Perfmon
Save the counters in a cfg file and then next time you open this file you will have all the counters available

Useful Memory Counters
SQL Server: Buffer Manager
Page Life Expectancy
Checkpoint Pages/Sec
Free Pages
Lazy Writes/Secgg
Memory Manager: Memory Grants Pending
Process: Working Set
Memory: Available MBytes
Useful Network Counters
Network Adapter: Current bandwith
Network Adapter: Bytes Total/sec
Network Adapter: Output Queue Length
Useful CPU Counters
Processor % Privileged Time
Processor % Processor Time
Process:*
SQL Statistics: Batch Requests/Sec
Database: Transaction/Sec
SQL Statistics: Compiles/Sec

Useful Process Counters
IO Data Bytes/sec
% Processor Time
Working Set
Useful IOCounters
Logical Disk  (what SQL Server sees)
Physical Disk    (What the OS sees)
Avg Disk Sec/Read
Avg Disk Write
% Idle Time
Disk Transfer/sec

Power Settings
Switch from balanced to high performance

Performance Dashboard Reports
Set of SSRS performance eports that integrate into SSMS, needs to be installed, download from CodePlex

------------------------------------------------------------------------------------------------------------
Denzil
------------------------------------------------------------------------------------------------------------

Why Xevents?
SQL trace not as performant, reduce need for a debugger, learn internals and troubleshoot, common instrumentation
Xevents: events, Predicates, Actions, Targets
Targets
Event file:    async, disk    Writes events from buffer to disk
ETW file:    sync, disk    Used to correlate with Windows or app data
Event counter:    sync, memory    Counts all specified events
Ring Buffer:    async, memory    event data held in FIFO
Histogram:    async, memory    Sums event occurances based on column or action
Pair matching:    async, memory  Determince when a specified paired event does not occur in a matched set
Demo of how to setup extended events with the wizard as well as T-SQL
Debug channel exposes some advanced things to help you better troubleshoot
Demo of using extended events to track on a per sessionid basis, this is not possible with DMVs

System Health Session
1)Errors
Any error with severity > 20
Memory related errors
CLR allocation and virtual allocation failures
2)Waits
lathes > 15 seconds
Locks > 30 seconds
preemtive waits > 15 seconds
3)Monitors
Deadlocks
sp_Server_diagnostics
4)Ring Buffer Data
COnnectivity Errors
Security Errors
Memory broker
Memory Node OOM
Scheduler Monitor
sp_server_diagnostic_component
If System Health Session is somehow disabled, it will not affect a cluster failover
Spinlocks
If the first acquire is unsuccessful a collision happens causing a spin. then we try again, during this phase we are spinning, we backoff and try again. You cn use spinlock_backoff and spinlock_backoff_warning, spinlock_backoff_warning happens when we have been spinning for a while and the thread got kicked off the CPU
traceflag 3656 + symbols makes callstacks readable instead of just Hex. Symbol files resolve memory into actual SQL Server calls, you can download the symbol list from Microsoft
Showed how Microsoft redesigned locks by using partition_id so locks were not in just one bucket and spinning decreased a lot, you can enable this by running SQL Server with trace flag 1236

Next up was a slide about problems with plans in the cache.
Cache limit is 160K..kb 2964518, I looked this up and here is what is in that kb article about plans
SQL service (Memory Manager)    TF 8032    Entries in the plan cache are evicted because of growth in other caches or memory clerks. You might also encounter plan cache eviction when the cache has reached its maximum number of entries. In addition to this trace flag 8032, consider the optimize for ad hoc workloads server option and also the FORCED PARAMETERIZATION database option.
CMEMTHREAD
Creating a spatial index on a 3 billion row took 4 days, demo showed a smaller table and how it affected performance. Spinlocks wait was high, a lot of CMEMTHREAD wait types, spiking from1 CPU to another. After doing a DBCC STACKDUMP and then loading it in WinDbg, we could see that it was partitioned by node instead of CPU. When starting with trace flag T8048 performance was much better, all the waits vanished. The index creation went from 4 days to 5 hours after using trace flag T8048
The last part of the session was about Hekaton or better known by it's marketing term In-Memory OLTP. At least Denzil didn't say that to use Hekaton there are no application changes needed  :-)
In-Memory OLTP is a good fit for
Performance-critical OLTP (think order processing or trading)
High data-input rate (nicknamed “Shock Absorber”)
In-Memory OLTP as components of ETL
Session state management
Read scale
In-Memory OLTP is not a good fit for
No permission for code changes
App depends on locking behavior
Full data warehousing
Long-running reporting workload (use Columnstore instead)
Use a lot of XML manipulation/Full-Text searches
Heavily parallelized query
Constrained on memory
******** END OF NOTES  ****************************

All in all an excellent session,  but man am I wiped out  :-)


I went back to the hotel to drop off my laptop, it was actually dry for a change, not a raindrop in sight and about 55 degrees, a very pleasant evening. I had an hour to kill so I took some pics of Seattle, in the picture below you can see Pike Place Market, you can see all of my Seattle 2014 pictures here on flickr

When I got back to the Seattle convention center, the welcome reception started

There were a ton of people at this reception, I saw a bunch of old friends and acquaintances.
I didn't stay that long because I had dinner plans with some MVPs (Adam Machanic, Davide Mauri, Marco Russo, Peter Myers and Alberto Ferrari). We went to a restaurant named Rione XIII, this restaurant is the only one that had burrata on the menu, the food was excellent, you can find the menu here: Rione XIII menu
After being up for over 21 hours I finally went to bed. Guess what, four hours later, I am awake again....writing this post...by the time I am used to this jet lag, it will be time to fly back to Princeton again

My 3 favorite sessions at PASS Summit 2014

These were my 3 favorite sessions at the PASS Summit 2014. I attended 13 sessions and most of them, were good, some of them had a demo fail and one of them had several demos fail. So here are the 3 I liked the most, they are in order that I attended them

 [AD-400] Query Tuning Mastery: Manhandling Parallelism, 2014 Edition
Speaker:  Adam Machanic 
This was an excellent session, the content was superb and the delivery was top-notch. I was a little sad when the session ended, I wish it would continue.
You can find the demos here: http://sqlblog.com/blogs/adam_machanic/archive/2014/11/06/pass-summit-2014-manhandling-parallelism-demos.aspx

[DBA-500-HD] Inside SQL Server I/O 
Speaker(s):  Bob Ward 
What do I have to see that anyone who attended any of Bob Ward's previous sessions  at the PASS Summit, doesn't already know. you HAVE to attend his sessions, don't worry if the content might be to advanced for you. Fast paced, a lot of cool stuff shown and excellent delivery
You can find the demos here: http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=7057

[DBA-305] Working with Very Large Tables Like a Pro in SQL Server 2014
Speaker:  Guy Glantser 
First session I ever attended by Guy Glantser. The delivery was perfect, the content was to the point and explained in simple steps.
You can find the demos here: http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6519

I also want to mention the following session, this is a pre-conference session so you won't be able to find the recording

Troubleshoot Customer Performance Problems Like a Microsoft Engineer 
Speaker(s):  Tim Chapman  Denzil Ribeiro 
Excellent session with a lot of good stuff, cool demos and tools were shown

Of course you might have attended a better session, but since it is impossible to attend all sessions I onlyknow about those that I attended, but you can leave me a comment telling me which ones you liked the best

What is a valid variable name in T-SQL?

On twitter Adam Machanic posted a question if anyone has a document that details what a valid variable name is, what is allowed and what is not allowed.
Did you know that you can have a variable named @@ or @@@@?

DECLARE @ int=5,@@ int
SET @@=@
DECLARE @@@@ int=@*@@
DECLARE @$$ int=@@@@
SELECT  @$$,@@@@

Running that will print  25 25
There is a Books On Line page that gives some details but it is not very explicit, you can find that page here Identifiers
But what is valid? Let's write a quick SQL script, we are going to use the spt_values in the master database to quickly generate a SQL statement which we can execute

SELECT number,'declare @' + CHAR(CONVERT(VARCHAR(5),number)) +' int' AS stmt
INTO #test
FROM master..spt_values WHERE type='p'

SELECT * from #test

First step is to dump what we want into a temporary table

Running that will give you something like this if you have the output in grid not text

Don't be fooled by that the first 33 rows, those will be generated as @ and will appear valid, those are non printable characters

So let's continue with our SQL script after you created the temp table, run the following, if there is an error, the script will catch it and print the character number

DELETE #test WHERE stmt IS NULL
DELETE #test WHERE number < 33
DELETE #test WHERE stmt like '%''%' OR stmt LIKE '"'

DECLARE @stmt VARCHAR(100)
DECLARE @start int = 0,@end int =(SELECT max(number) from #test)
WHILE @start <= @end
BEGIN
    SELECT @stmt = stmt from #test where number = @start
    BEGIN TRY
        EXEC(@stmt)
    END TRY
        BEGIN CATCH 
        PRINT 'error ' + right('000' + CONVERT(VARCHAR(3),@start),3) + ' ' + @stmt
    END CATCH

    SET @start+=1
END

Running that code will give you the following output

error 033 declare @! int
error 034 declare @" int
error 037 declare @% int
error 038 declare @& int
error 039 declare @' int
error 040 declare @( int
error 041 declare @) int
error 042 declare @* int
error 043 declare @+ int
error 044 declare @, int
error 045 declare @- int
error 046 declare @. int
error 047 declare @/ int
error 058 declare @: int
error 059 declare @; int
error 060 declare @< int
error 061 declare @= int
error 062 declare @> int
error 063 declare @? int
error 091 declare @[ int
error 092 declare @\ int
error 093 declare @] int
error 094 declare @^ int
error 096 declare @` int
error 123 declare @{ int
error 124 declare @| int
error 125 declare @} int
error 126 declare @~ int
error 127 declare @ int
error 128 declare @€ int
error 129 declare @ int
error 130 declare @‚ int
error 132 declare @„ int
error 133 declare @… int
error 134 declare @† int
error 135 declare @‡ int
error 136 declare @ˆ int
error 137 declare @‰ int
error 139 declare @‹ int
error 141 declare @ int
error 143 declare @ int
error 144 declare @ int
error 145 declare @‘ int
error 146 declare @’ int
error 147 declare @“ int
error 148 declare @” int
error 149 declare @• int
error 150 declare @– int
error 151 declare @— int
error 152 declare @˜ int
error 153 declare @™ int
error 155 declare @› int
error 157 declare @ int
error 161 declare @¡ int
error 162 declare @¢ int
error 163 declare @£ int
error 164 declare @¤ int
error 165 declare @¥ int
error 166 declare @¦ int
error 167 declare @§ int
error 168 declare @¨ int
error 169 declare @© int
error 171 declare @« int
error 172 declare @¬ int
error 173 declare @­ int
error 174 declare @® int
error 175 declare @¯ int
error 176 declare @° int
error 177 declare @± int
error 178 declare @² int
error 179 declare @³ int
error 180 declare @´ int
error 182 declare @¶ int
error 183 declare @· int
error 184 declare @¸ int
error 185 declare @¹ int
error 187 declare @» int
error 188 declare @¼ int
error 189 declare @½ int
error 190 declare @¾ int
error 191 declare @¿ int
error 215 declare @× int
error 247 declare @÷ int

So those are all invalid, as well as the single and double quote and everything between characters 0 and 32 since those are not really printable but how would you even use those?

So there you have it a quick and dirty script to test what is valid....

Exercise for you...expand this to use unicode......

Did you know you can pass false and true to SQL Server bit parameters?

I noticed a stored procedure with a bit parameter and the default was false, I have never seen this before in SQL Server. You can do this in a language like c# but not in SQL Server right?

Let's take a look

Create this proc

create procedure prTest
@BitTrue bit = true,
@BitFalse bit = False,
@BitNotFalse bit = -2
as
Select @BitTrue as BitTrue, @BitFalse as BitFalse,
@BitNotFalse as BitNotFalse
GO

Now execute the sored procedure

EXEC prTest

BitTrue BitFalse BitNotFalse
1 0 1


As you can see true gets converted to 1, false gets converted to 0 and any number that is not 0 will be converted to 1 as well

What happens when you pass in false for the parameter that had a default of true?

EXEC prTest @BitTrue = false

BitTrue BitFalse BitNotFalse
0 0 1



How about variables..can you use true and false with bit datatype variables?
Let's try it out

DECLARE @BitTrue bit 
SET @BitTrue = true

Msg 207, Level 16, State 1, Line 2
Invalid column name 'true'.

DECLARE @BitTrue bit 
SET @BitTrue = false

Msg 207, Level 16, State 1, Line 3
Invalid column name 'false'.


As you can see you can  use true and false with parameters in a proc but not with variables.

My non SQL blog is up

In case you are interested in non SQL Server related stuff, I have started a blog about non SQL stuff. You can find it here http://denis-gobo.squarespace.com

See you there

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

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

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

Microsoft SQL Server, codename "Denali", will be the last release to support OLE DB, ODBC is the new new thing
Looks like OLE DB has run its course, ODBC is the new thing

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/

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

Day 1: Date and time
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

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

6 new SQL Server Denali CTP3 posts

I have created 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

A Quick look at the new EOMONTH function in SQL Server Denali CTP3

DATEFROMPARTS and DATETIMEFROMPARTS functions in SQL Server Denali CTP3

ColumnStore Index limitations in SQL Server Denali CTP3

Concat function in SQL Server Denali CTP3

Format function in SQL Server Denali CTP3

Post I created about SQL Server Denali

Today at PASS the SQL Server team has publicly announced the first Community Technology Preview of the next version of SQL Server code named Denali. You can download it here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

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

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

10 must read SQL Server posts by Ted Krueger

A buddy of mine Ted Krueger writes some really interesting SQL Server posts. Most of these posts focus more on administration than on developer stuff but still stuff that is useful to a developer. Here are 10 of my favorites, I liked these and you will too!


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

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

• 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

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

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