What's new in SQL Server 2012?

What's new in SQL Server 2012?

Rather than regurgitate a press release or add another list, here are some others who have already formed their opinions.

Top 12 Features of SQL Server 2012 (Joey D'Antoni)

My Top 5 SQL Server 2012 Features (Aaron Bertrand)

Top 10 New Features in SQL 2012 (John Weston)

Using SQL Server 2012 T-SQL New Features (Henry He)

T-SQL Improvements in SQL Server 2012 (Jonathan Allen)

Cool New SQL Server 2012 Metadata (Kalen Delaney)

Differences between SQL Server 2008 and SQL Server 2012 (Virendra Yaduvanshi)

SQL Server 2012 - Why it Rocks (Michael Corey)

Upgrading to SQL 2012 - Ten things you don't want to miss

SQL IO Latencies

I've spent time recently looking into the performance of visualized SQL instances on SAN storage. The systems present a myriad of possibilities with regards to how exposed drives are configured i.e. physical disks, LUNs and virtual drives. Smart SAN solutions can move data around according to frequency of use, placing the most accessed data on the fastest storage. Virtual Hosts can reallocate resources amongst guests e.g. if a failure occurs or for a data processing window. Resources available to a VM can therefore change, maybe even without the guest OS being aware.

Anyway, Paul Randal has a script for looking at your I/O throughput as seen by SQL Server.
It's a lot quicker than using Perfmon (Performance Monitor).

How to examine IO subsystem latencies from within SQL Server

Copying large files - Fastcopy

If copying large files you might want to consider 'how'.
It comes down to Buffered vs. Unbuffered Input/Output (I/O).

Unbuffered copies are built-in to Win 2008 R2 and the Win 7 version

To perform unbuffered copies on an older system the following tools are useful.


FastCopy  - Download

Eseutil (exchange install) - Download


Ref -
How to copy very large files across a slow or unreliable network

Tools - Nanozip

A great compression tool which allows you to control how many processors it uses -

c:\>nz a -p2 nanozip.nz "x:\export\*.*"
NanoZip 0.09 alpha/Win32  (C) 2008-2011 Sami Runsas  www.nanozip.net
Intel(R) Xeon(R) CPU 5110 @ 1.60GHz|21926 MHz|#4|1208/2047 MB
Archive: nanozip.nz
Threads: 4, memory: 512 MB, IO-buffers: 4+1 MB
Compressor #0: nz_optimum1 [251 MB]
Compressor #1: nz_optimum1 [251 MB]
Compressed 56 357 924 728 into 4 336 512 403 in 5h 39m 13.10s, 2704 KB/s
IO-in: 31m 53.80s, 28 MB/s. IO-out: 10.15s, 407 MB/s

Download

Finding Default Column Constraints where the columns allow NULLs

Finding Default COlumn Constraints where the columns allow NULLs Investigating a database I wrote these to find some design inconsistencies.

I plan to make a version for foreign key constraints too. SQL 2000 script

SELECT	  
	  u.name AS OwnerName
	, tab.name AS TableName
	, col.name AS ColumnName
	, col.isnullable
	, con.name AS DefaultName 
	, com.text AS DefaultValue
FROM sysobjects tab
INNER JOIN sysusers u ON tab.uid = u.uid
INNER JOIN syscolumns col ON col.id = tab.id
INNER JOIN sysobjects con ON con.id = col.cdefault 
       AND con.xtype = 'D'
INNER JOIN syscomments com ON com.id = con.id 
 LEFT JOIN syscolumns dfc ON dfc.id = com.id
WHERE col.isnullable = 1
ORDER BY 1,2

SQL 2005+ script

SELECT 
	 Tab.name AS Tablename
	,Col.name AS Columnname
	,Col.is_nullable
	,Con.name AS DefaultName
	,[Definition] AS DefaultValue
FROM	sys.all_columns Col
INNER JOIN sys.tables Tab 
		ON Col.object_id = Tab.object_id
INNER JOIN sys.default_constraints Con
		ON Col.default_object_id = Con.object_id
WHERE col.is_nullable = 1
ORDER BY 1,2

Similar to the above, most can be done from INFORMATION_SCHEMA view ins sql 2005 (with the exception of the default name)

SELECT
	 TABLE_SCHEMA AS SchemaName
	,TABLE_NAME AS TableName
	,COLUMN_NAME AS ColumnName
	,IS_NULLABLE
	,COLUMN_DEFAULT AS DefaultValue
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_DEFAULT IS NOT NULL
  AND IS_NULLABLE = 'YES'
ORDER BY 1,2,3

Collation Mismatch : I think it's one of those deja vu things

This error surfaced once again today...

Msg 468, Level 16, State 9, Server SERVER1, Line 6 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. 

Here is a quick query of looking at COLLATION and COMPATIBILITY LEVEL differences between a server and the databases hosted on it. I'm looking at compatibility level too as in my case I correctly suspected that the databases concerned were migrated from another server.

SELECT 
  @@SERVERNAME AS ServerName
 ,SERVERPROPERTY('Collation') AS ServerCollation
 ,(10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) AS ServerVersion
 ,[Name] AS DBName
 ,DATABASEPROPERTYEX([Name],'Collation') AS DBCollation
 ,[cmptlevel] AS DBCompatibilityLevel
 ,CASE WHEN SERVERPROPERTY('Collation') <> DATABASEPROPERTYEX([Name],'Collation') THEN 'Mismatch' ELSE 'Match' END AS CollationSettings
 ,CASE WHEN (10 * (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) <> [cmptlevel] THEN 'Mismatch' ELSE 'Match' END AS CompatibilitySettings
FROM sysdatabases

I can actually solve my specific problem by using a COLLATE clause on the join condition (as demonstrated back in 2007).

This is because I'm querying SQL System tables across databases (the master and user databases having different collations having the user databases being migrated from another server).

I think it's one of those deja vu things, an article I wrote on Collation for SQL Server Club (just don't talk about SQL Server Club).

Function - msdb.dbo.agent_datetime

How did I miss this function?

For those that ever went through the pain of interpretting the run_date and run_time columns stored in msdb's sysjobhistory table, this function is a godsend. It returns a DATETIME format, that you can simply add the job duration to.

It appeared back in SQL 2005, and is demoed by this query ...

SELECT 
 run_date
,run_time
,msdb.dbo.agent_datetime(run_date,run_time) 
FROM msdb.dbo.sysjobhistory
ORDER BY run_date ,run_time

Various methods exist for doing the same in SQL 2000, all involving CAST/CONVERT to manipulate the strings and adding leading zeros where they are missing.
Here is my effort ...

SELECT 
 run_date
,run_time
,STUFF(STUFF(STR(run_date, 8, 0),5,0,'-'),8,0,'-') AS run_date_dateformat
,STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,run_time),6), 3, 0, ':'), 6, 0, ':') AS run_date_timeformat
,CAST(STUFF(STUFF(STR(run_date, 8, 0),5,0,'-'),8,0,'-') + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,run_time),6), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS run_datetime_complete
FROM     msdb.dbo.sysjobhistory
ORDER BY run_date ,run_time