Table Value Constructor in T-SQL

SQL Server 2008 had introduced one interesting feature that helps to create “inline” tables. It’s a table value constructor. It is commonly used when you need to insert few values or few rows of values into the table. Something like this:

But there’s another case where you can use this kind of tables. For example, you need to update few records, you know their IDs and values to be put to table. Here you can write your update like the following code:

In this update command I join table Employee from database with “inline” table T that was constructed in-flight from 4 pairs of values. It’s much easier to write one command, especially if it’s rather complicated, than to repeat it again and again.

Update 2015-12-10
You can even create a temporary table with table value constructor like this.

HDD Cluster Size and SQL Server Performance

During my #sqlweek I was asked few times what HDD parameters (sector or cluster sizes) would be better for SQL Server installations. There’s a good article on Technet that explains Windows disk parameters in details, so I don’t need to retell it. Just read the article Disk Partition Alignment Best Practices for SQL Server thoroughly. The main points discussed are partition alignment, stripe unit size and file allocation unit size (cluster size).

As for the HDD cluster size, it is said that “an appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.”

Slide Decks and Demos for #sqlweek 2015

Here are my presentations and demo scripts/projects that were used on my sessions on SQL Saturdays or user group meetings during my #sqlweek from August 29 till September 5, 2015.

Slide decks (in chronological order):

29.08.2015 SQL Sat Oslo, Norway – Transaction Log Internals and Troubleshooting

31.08.2015 Wroclaw, Poland – Transaction Log Internals and Troubleshouting

01.09.2015 Copenhagen, Denmark – Visual Studio for SQL developers

02.09.2015 Gent, Belgium – Visual Studio for SQL developers

03.09.2015 Berlin, Germany – Visual Studio for SQL developers

03.09.2015 Berlin, Germany – Transaction Log Internals and Troubleshooting

05.09.2015 SQLSat Goteborg, Sweden – Transaction Log Internals and Troubleshooting

 

Demo files for the session “Transaction Log Internals and Troubleshooting”:

Demo – Transaction log internals and troubleshooting

Demo files for the session “Visual Studio for SQL Developers”:

ClientsDB_setup

ClientsDBSolution