Viewing sample rows in all used tables

During database migration projects there is sometimes the need to search through databases looking for clues to assist with the migration i.e. connection strings, configuration settings etc.

This can be a daunting task, especially when a database holds hundreds or even thousands of tables.

The following query may help if you find yourself in this situation.

1. Run the following query against the database. It should identify non-empty tables and generate the relevant TSQL select queries.

SELECT 'SELECT TOP 5 ''[' + OBJECT_SCHEMA_NAME(a.object_id) + '].[' + a.name + ']'' AS Table_Name, * FROM [' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(a.object_id) + '].[' + a.name + '] WITH (nolock);'  
FROM sys.objects a, sysindexes b
WHERE 1=1
AND a.object_id = b.id -- match objects
AND b.indid IN (0,1) -- 0 is heap 1 is clustered
AND a.type_desc = 'USER_TABLE' -- only user tables
AND b.dpages <> 0 -- data in the table
ORDER BY a.name;

image1
2. Run the generated TSQL select queries and progress with your investigations.

image2

SQL Collation Names Guide

Pre SQL Server 2008, matching a SQL collation to an existing build could be a bit of a challenge. Typically you would have to research which long SQL collation name would give you the short SQL collation name:
image1

As shown below, in SQL Server 2008 onwards you now get presented the short name with the associated long name to make the process simpler:
image2

The following table made up from here and here, may help you pick the correct one; or you could try one of these queries:
 

SELECT * 
FROM master.sys.fn_helpcollations()
WHERE name LIKE 'SQL%'

 

SELECT * 
FROM ::fn_helpcollations()
WHERE name LIKE 'SQL%'
ID Short Name Long Name
30 SQL_Latin1_General_Cp437_BIN Binary order, for use with the 437 (U.S. English) character set.
31 SQL_Latin1_General_Cp437_CS_AS Dictionary order, case-sensitive, for use with the 437 (U.S. English) character set.
32 SQL_Latin1_General_Cp437_CI_AS Dictionary order, case-insensitive, for use with the 437 (U.S. English) character set.
33 SQL_Latin1_General_Pref_CP437_CI_AS Dictionary order, case-insensitive, uppercase preference, for use with the 437 (U.S. English) character set.
34 SQL_Latin1_General_Cp437_CI_AI Dictionary order, case-insensitive, accent-insensitive, for use with the 437 (U.S. English) character set.
40 SQL_Latin1_General_Cp850_BIN Binary order, for use with the 850 (Multilingual) character set.
41 SQL_Latin1_General_Cp850_CS_AS Dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
42 SQL_Latin1_General_Cp850_CI_AS Dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
43 SQL_Latin1_General_Pref_CP850_CI_AS Dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
44 SQL_Latin1_General_Cp850_CI_AI Dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set.
49 SQL_1Xcompat_CP850_CI_AS Strict compatibility with version 1.x case-insensitive databases, for use with the 850 (Multilingual) character set.
51 SQL_Latin1_General_Cp1_CS_AS Dictionary order, case-sensitive, for use with 1252 character set.
52 SQL_Latin1_General_Cp1_CI_AS Dictionary order, case-insensitive, for use with 1252 character set.
53 SQL_Latin1_General_Pref_CP1_CI_AS Dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
54 SQL_Latin1_General_Cp1_CI_AI Dictionary order, case-insensitive, accent-insensitive, for use with 1252 character set.
55 SQL_AltDiction_Cp850_CS_AS Alternate dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
56 SQL_AltDiction_Pref_CP850_CI_AS Alternate dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
57 SQL_AltDiction_Cp850_CI_AI Alternate dictionary order, case-insensitive, accent-insensitive, for use with the 850 (Multilingual) character set.
58 SQL_Scandinavian_Pref_Cp850_CI_AS Scandinavian dictionary order, case-insensitive, uppercase preference, for use with the 850 (Multilingual) character set.
59 SQL_Scandinavian_Cp850_CS_AS Scandinavian dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.
60 SQL_Scandinavian_Cp850_CI_AS Scandinavian dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
61 SQL_AltDiction_Cp850_CI_AS Alternate dictionary order, case-insensitive, for use with the 850 (Multilingual) character set.
81 SQL_Latin1_General_Cp1250_CS_AS Dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
82 SQL_Latin1_General_Cp1250_CI_AS Dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
83 SQL_Czech_Cp1250_CS_AS Czech dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
84 SQL_Czech_Cp1250_CI_AS Czech dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
85 SQL_Hungarian_Cp1250_CS_AS Hungarian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
86 SQL_Hungarian_Cp1250_CI_AS Hungarian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
87 SQL_Polish_Cp1250_CS_AS Polish dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
88 SQL_Polish_Cp1250_CI_AS Polish dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
89 SQL_Romanian_Cp1250_CS_AS Romanian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
90 SQL_Romanian_Cp1250_CI_AS Romanian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
91 SQL_Croatian_Cp1250_CS_AS Croatian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
92 SQL_Croatian_Cp1250_CI_AS Croatian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
93 SQL_Slovak_Cp1250_CS_AS Slovak dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
94 SQL_Slovak_Cp1250_CI_AS Slovak dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
95 SQL_Slovenian_Cp1250_CS_AS Slovenian dictionary order, case-sensitive, for use with the 1250 (Central European) character set.
96 SQL_Slovenian_Cp1250_CI_AS Slovenian dictionary order, case-insensitive, for use with the 1250 (Central European) character set.
105 SQL_Latin1_General_Cp1251_CS_AS Dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set.
106 SQL_Latin1_General_Cp1251_CI_AS Dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set.
107 SQL_Ukrainian_Cp1251_CS_AS Ukrainian dictionary order, case-sensitive, for use with the 1251 (Cyrillic) character set.
108 SQL_Ukrainian_Cp1251_CI_AS Ukrainian dictionary order, case-insensitive, for use with the 1251 (Cyrillic) character set.
113 SQL_Latin1_General_Cp1253_CS_AS Dictionary order, case-sensitive, for use with the 1253 (Greek) character set.
114 SQL_Latin1_General_Cp1253_CI_AS Dictionary order, case-insensitive, for use with the 1253 (Greek) character set.
120 SQL_MixDiction_Cp1253_CS_AS Mixed dictionary order, for use with the 1253 (Greek) character set.
121 SQL_AltDiction_Cp1253_CS_AS Dictionary order, case-sensitive, accent-sensitive, for use with the 1253 (Greek) character set.
124 SQL_Latin1_General_Cp1253_CI_AI Dictionary order, case-insensitive, accent-insensitive, for use with the 1253 (Greek) character set.
129 SQL_Latin1_General_Cp1254_CS_AS Dictionary order, case-sensitive, for use with the 1254 (Turkish) character set.
130 SQL_Latin1_General_Cp1254_CI_AS Dictionary order, case-insensitive, for use with the 1254 (Turkish) character set.
137 SQL_Latin1_General_Cp1255_CS_AS Dictionary order, case-sensitive, for use with the 1255 (Hebrew) character set.
138 SQL_Latin1_General_Cp1255_CI_AS Dictionary order, case-insensitive, for use with the 1255 (Hebrew) character set.
145 SQL_Latin1_General_Cp1256_CS_AS Dictionary order, case-sensitive, for use with the 1256 (Arabic) character set.
146 SQL_Latin1_General_Cp1256_CI_AS Dictionary order, case-insensitive, for use with the 1256 (Arabic) character set.
153 SQL_Latin1_General_Cp1257_CS_AS Dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
154 SQL_Latin1_General_Cp1257_CI_AS Dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
155 SQL_Estonian_Cp1257_CS_AS Estonian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
156 SQL_Estonian_Cp1257_CI_AS Estonian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
157 SQL_Latvian_Cp1257_CS_AS Latvian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
158 SQL_Latvian_Cp1257_CI_AS Latvian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
159 SQL_Lithuanian_Cp1257_CS_AS Lithuanian dictionary order, case-sensitive, for use with the 1257 (Baltic) character set.
160 SQL_Lithuanian_Cp1257_CI_AS Lithuanian dictionary order, case-insensitive, for use with the 1257 (Baltic) character set.
183 SQL_Danish_Pref_Cp1_CI_AS Danish/Norwegian dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
184 SQL_SwedishPhone_Pref_Cp1_CI_AS Finnish-Swedish (Phone), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 184 on Code Page 1252 for non-Unicode Data.
185 SQL_SwedishStd_Pref_Cp1_CI_AS Finnish-Swedish (Standard), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 185 on Code Page 1252 for non-Unicode Data.
186 SQL_Icelandic_Pref_Cp1_CI_AS Icelandic dictionary order, case-insensitive, uppercase preference, for use with 1252 character set.
210 SQL_EBCDIC037_CP1_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 210 on Code Page 1252 for non-Unicode Data
211 SQL_EBCDIC273_CP1_CS_AS German-PhoneBook, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 211 on Code Page 1252 for non-Unicode Data
212 SQL_EBCDIC277_CP1_CS_AS Danish-Norwegian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 212 on Code Page 1252 for non-Unicode Data
213 SQL_EBCDIC278_CP1_CS_AS Finnish-Swedish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 213 on Code Page 1252 for non-Unicode Data
214 SQL_EBCDIC280_CP1_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 214 on Code Page 1252 for non-Unicode Data
215 SQL_EBCDIC284_CP1_CS_AS Modern-Spanish, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 215 on Code Page 1252 for non-Unicode Data
216 SQL_EBCDIC285_CP1_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 216 on Code Page 1252 for non-Unicode Data
217 SQL_EBCDIC297_CP1_CS_AS French, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 217 on Code Page 1252 for non-Unicode Data

Skip Instance IDs for SQL Server 2005 installs

If you ever have the requirement to use a specific ID for a SQL Server 2005 install e.g. MSSQL.5, what can you do?

Good old SQL Server 2005 didn’t give you the option to choice an Instance ID, unlike SQL Server 2008 onwards, so you can either:

1. Install multiple instances until you get to MSSQL.5; or
2. Simply add entries in the Instance Names section of the registry prior to performing your SQL install and remove them afterwards.

An example is shown below and the next SQL install will start at MSSQL.5
image1
A sample registry reg file to perform the task would be
image2
As always, use caution when manually updating the registry.

Quickly script and transfer logins

You may come across the need to migrate one or all logins to another instance.

Here are some scripts that may be able to assist you with that task without you needing to know the login passwords.

Remember to review and exclude the logins you don’t need to transfer.

1. Script all SQL Logins

-- SQL Server 2008 onwards
SELECT 
  'CREATE LOGIN [' + [name] + '] WITH PASSWORD='
+ CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED, DEFAULT_DATABASE=[' + default_database_name 
+ '], DEFAULT_LANGUAGE=[' + default_language_name 
+ '], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', SID='
+ CONVERT(varchar(MAX),sid,1)
+ '; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.sql_logins
WHERE 1=1
AND [name] NOT LIKE '##%'
AND [name] <> 'sa'
ORDER BY [name];

-- Pre SQL Server 2008
SELECT 
  'CREATE LOGIN [' + [name] + '] WITH PASSWORD='
+ master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED, DEFAULT_DATABASE=[' + default_database_name 
+ '], DEFAULT_LANGUAGE=[' + default_language_name 
+ '], CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', CHECK_POLICY=' + CASE WHEN is_policy_checked = 0 THEN 'OFF' ELSE 'ON' END
+ ', SID='
+ master.dbo.fn_varbintohexstr(sid)
+ '; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.sql_logins
WHERE 1=1
AND [name] NOT LIKE '##%'
AND [name] <> 'sa'
ORDER BY [name];

image1
2. Script the sa password to match on the other instance, if required

-- SQL Server 2008 onwards
SELECT 
  'IF (SELECT is_policy_checked FROM master.sys.sql_logins WHERE [name]=''sa'') = 1'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH CHECK_POLICY=OFF, PASSWORD = '	
+ CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED; ALTER LOGIN [sa] WITH CHECK_POLICY=ON;'
+ ' END'
+ ' ELSE'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH PASSWORD =' 
+  CONVERT(varchar(MAX),password_hash,1)
+ ' HASHED;'
+ ' END;'
FROM master.sys.sql_logins
WHERE 1=1
AND [name] = 'sa';

-- Pre SQL Server 2008
SELECT 
  'IF (SELECT is_policy_checked FROM master.sys.sql_logins WHERE [name]=''sa'') = 1'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH CHECK_POLICY=OFF, PASSWORD = '	
+ master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED; ALTER LOGIN [sa] WITH CHECK_POLICY=ON;'
+ ' END'
+ ' ELSE'
+ ' BEGIN'
+ ' ALTER LOGIN [sa] WITH PASSWORD =' 
+  master.dbo.fn_varbintohexstr(password_hash)
+ ' HASHED;'
+ ' END;'
FROM master.sys.sql_logins
WHERE 1=1
AND [name] = 'sa';

image2
3. Script the Windows logins

SELECT
  'CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' 
+ COALESCE(default_database_name,'master')
+ '], DEFAULT_LANGUAGE=[' + COALESCE(default_language_name,'us_english')
+ ']; ' + CASE WHEN is_disabled = 1 THEN 'ALTER LOGIN [' + name + '] DISABLE;' ELSE '' END
FROM master.sys.server_principals
WHERE 1=1
AND type_desc IN ('WINDOWS_LOGIN','WINDOWS_GROUP')
ORDER BY [name];

image3
4. Script the server role memberships of the logins

-- SQL Server 2012 onwards
SELECT
 'ALTER SERVER ROLE [' + a.[name] + '] ADD MEMBER [' + b.[name] + '];'
FROM master.sys.server_principals a, master.sys.server_principals b, master.sys.server_role_members c
WHERE 1=1
AND a.principal_id = c.role_principal_id
AND b.principal_id = c.member_principal_id
ORDER BY a.name, b.name;

-- Pre SQL Server 2012
SELECT
 'EXEC sp_addsrvrolemember [' + b.[name] + '],[' + a.[name] + '];'
FROM master.sys.server_principals a, master.sys.server_principals b, master.sys.server_role_members c
WHERE 1=1
AND a.principal_id = c.role_principal_id
AND b.principal_id = c.member_principal_id
ORDER BY a.[name], b.[name];

image4
5. Script the server securables

SELECT a.state_desc + ' ' + a.[permission_name] + ' TO [' + b.name + ']' COLLATE Latin1_General_CI_AS AS server_securables
FROM sys.server_permissions a, sys.server_principals b
WHERE 1=1
AND a.grantee_principal_id = b.principal_id
AND a.[permission_name] NOT IN ('CONNECT SQL')
AND b.name NOT LIKE '##%'
AND b.name <> 'public'
ORDER BY b.name;

 
When a database is restored the users should automatically resolve to the logins you’ve transferred, unless they were orphaned to being with.
 
The following script shows which database users are orphaned.

DECLARE @orphaned_users TABLE (DatabaseName nvarchar(128), UserName nvarchar(128));

DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM sys.databases WHERE state_desc = 'ONLINE'
OPEN item_cursor
DECLARE @cursor nvarchar(128)
DECLARE @sql nvarchar(MAX);
FETCH NEXT FROM item_cursor INTO @cursor
WHILE @@FETCH_STATUS = 0 
BEGIN

IF (CAST(SERVERPROPERTY('ProductVersion') AS varchar(10))) NOT LIKE '9%'
AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(10))) NOT LIKE '10%'
BEGIN
SET @sql = 'USE [' + @cursor + '];
SELECT ''' + @cursor + ''', UserName = [name]
FROM sysusers  
WHERE 1=1
AND  issqluser = 1   
AND (sid IS NOT NULL AND sid <> 0x0)  
AND (LEN(sid) <= 16)  
AND SUSER_SNAME(sid) IS NULL
UNION
SELECT ''' + @cursor + ''', UserName = [name]
FROM sys.database_principals
WHERE 1=1
AND authentication_type_desc = ''WINDOWS''
AND  [name] NOT LIKE ''dbo''
EXCEPT
(SELECT ''' + @cursor + ''',DP.[name] FROM sys.database_principals DP
INNER JOIN sys.server_principals SP ON DP.[sid] = SP.[sid] 
WHERE 1=1
AND DP.authentication_type_desc = ''WINDOWS'')';
END
ELSE
BEGIN
SET @sql = 'USE [' + @cursor + '];
SELECT ''' + @cursor + ''', UserName = [name]
FROM sysusers  
WHERE 1=1
AND  issqluser = 1   
AND (sid IS NOT NULL AND sid <> 0x0)  
AND (LEN(sid) <= 16)  
AND SUSER_SNAME(sid) IS NULL
UNION
SELECT ''' + @cursor + ''', UserName = [name]
FROM sys.database_principals
WHERE 1=1
AND type_desc IN (''WINDOWS_USER'',''WINDOWS_GROUP'')
AND  [name] NOT LIKE ''dbo''
EXCEPT
(SELECT ''' + @cursor + ''',DP.[name] FROM sys.database_principals DP
INNER JOIN sys.server_principals SP ON DP.[sid] = SP.[sid] 
WHERE 1=1
AND DP.type_desc IN (''WINDOWS_USER'',''WINDOWS_GROUP''))';

INSERT INTO @orphaned_users (DatabaseName, UserName)
EXEC(@sql);
END

FETCH NEXT FROM item_cursor INTO  @cursor
END
CLOSE item_cursor;  
DEALLOCATE item_cursor; 

SELECT DatabaseName AS database_name, UserName AS orphaned_user
FROM @orphaned_users ORDER BY DatabaseName,UserName;

Maximum server memory change taking a long time, don’t panic

So, you’ve changed the Maximum server memory option and the server property box appears to have hung, what do you do?
image1
The short answer: wait.

You can view the current errorlog e.g. xp_readerrorlog, and once a cachestore flush of ‘Object Plans‘, ‘SQL Plans‘ and ‘Bound Trees‘ has completed, the property box will finally close.

Click to enlarge.
image2
As per the above screenshot, this took well over 30 minutes for this VM which was placed on an over provisioned host.

Quickly find missing indexes in cache

Yes there are caveats with adding indexes ad hoc and there are many posts regarding extracting a relevant query, testing with SET STATISTICS IO … etc to validate the proposed index.

One query to peek into the cache is:
 

SELECT
 cp.objtype
,cp.usecounts
,cp.refcounts
,cp.size_in_bytes
,qt.text AS query_text
,qp.query_plan AS query_plan
FROM master.sys.dm_exec_cached_plans AS cp (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS qt
WHERE CAST(query_plan AS nvarchar(MAX)) LIKE N'%MissingIndex%'
ORDER BY cp.usecounts DESC, cp.refcounts DESC
OPTION (RECOMPILE, MAXDOP 1);

image1
When clicking on the query_plan xml you should get something similar to the below.
image2
If you don’t; try place the xml in a text file and give the text file the extension .sqlplan e.g. missing_index.sqlplan
image3
Right click on the reported missing index and select Missing Index Details…
image4
You now need to give the index a name that follows your index naming standards
image5
and run the query
image6
As always, you should validate the effectiveness of the new index i.e. before and after using SET STATISTICS IO ON etc, before deciding to keep it.

The mystique of the DELETE FROM FROM

Recently I came across a CRM query which used FROM twice in the DELETE statement.

Below is an example of it in action and yes a warning the results can be a bit perplexing.
 

-- Create a table
 CREATE TABLE your_table
 (
  id int IDENTITY (1,1)
 ,y_n char(1) NULL
 );
 GO
 -- Create a view
 CREATE VIEW your_view
 AS
 SELECT id,y_n
 FROM your_table
 WHERE y_n = 'y';
 GO

 -- Insert some data into the table
 INSERT INTO your_table (y_n)
 VALUES
  ('y')
 ,('y')
 ,('y')
 ,('y')
 ,('n');
 GO

image1_1

-- First DELETE test
-- With the view after the first FROM with 'n'
BEGIN TRAN
DELETE 
FROM your_view
FROM your_table t
WHERE t.y_n = 'n';

 -- You get 1 row returned
SELECT * FROM [your_table] WITH (NOLOCK);

ROLLBACK

image2

-- Second DELETE test
-- With the view after the first FROM with 'y'
BEGIN TRAN
DELETE 
FROM your_view
FROM your_table t
WHERE t.y_n = 'y';

-- You also get the same row returned
SELECT * FROM your_table WITH (NOLOCK);

ROLLBACK

image3

-- Third DELETE test
-- With the table after the first FROM with 'n'
BEGIN TRAN
DELETE 
FROM your_table
FROM your_view v
WHERE v.y_n = 'n';

-- You get all rows returned
SELECT * FROM your_table WITH (NOLOCK); 

ROLLBACK

image4

-- Final DELETE test
-- With the table after the first FROM with 'y'
BEGIN TRAN
DELETE 
FROM your_table
FROM your_view v
WHERE v.y_n = 'y';

-- All rows are now deleted
SELECT * FROM your_table WITH (NOLOCK);

ROLLBACK

image5
To remove the table and view use:

DROP VIEW your_view;
DROP TABLE your_table;

image6

Microsoft Azure SQL Server 2014 AlwaysOn Gallery option

Microsoft has recently added SQL Server 2014 AlwaysOn to the Microsoft Azure Gallery and the following screenshots go through the process of using it with the default settings.

The follow description is supplied from the gallery:

‘Automates the deployment of a SQL Server AlwaysOn Availability Group for high availability of SQL Server. It provisions 2 SQL Server 2014 Enterprise replicas (primary and secondary) and 1 witness file share in a Windows Cluster. It also provisions 2 Domain Controller replicas (primary and secondary). In addition, it configures an Availability Group Listener for clients to connect to the primary SQL Server replica.’

It should be noted that this gallery option will be very expensive to run long term due to the included Microsoft SQL licence costs. I suspect Microsoft should also offer a SQL Server 2014 Evaluation Edition alternative to allow cheaper testing and the ability to use in-house SQL licensing if the testing works out.

image1
image2
image3
image4
Warning: As mentioned, this will be expensive to run long term as shown in the pricing below (as at 23/08/2014):
image5
image6
image7
image8
image9
image10
From clicking Create the process took ~ 2 hours.

You will see the following progress on the Startboard:
image11
The Notifications section shows more info on the creation process
image12
image13
Here is what gets created:
image14
and SQL Server looks like:
image15

SQL Server ODBC system error code 126

Recently at a site which had Windows Server 2003 R2 64bit SP2, the following error message was received when attempting to create a new SQL Server ODBC data source:

The setup routines for the SQL Server ODBC driver could not be loaded due to system error code 126.

image1
image2
image3
The resolution in this case was to locate, right click on C:\WINDOWS\inf\mdac.inf and select Install.
image4
You may need to locate the media to complete the process.
image5

Object Explorer Details footer section

An often over looked feature of SQL Server Management Studio is the footer section of Object Explorer Details.

You can open Object Explorer Details via the View menu or pressing F7.

image1

Depending on where you click in Object Explorer and Object Explorer Details, the footer in Object Explorer Details changes.

Some examples are:

image2

image3

image4

In addition to the footer; there are hidden columns in Object Explorer Details, via a right click, which may make your investigative life a bit easier i.e.

image5
image6