Storage, although cheap is still a limited resource. In our development and sustenance environments, we often get databases from multiple different sources which we need to use to troubleshoot certain issues or carry out some research and/or other development activity on. The backups of these databases take up considerable amount of space and we ended up either using 3rd party tools, or ensuring that the DBA uses a file compression tool to reduce the disk space consumption. When SQL Server 2008 was released, it’s backup compression feature came to our rescue.

Backup Compression (Single-use)

• Starting SQL Server 2008 Enterprise Edition and above, Microsoft SQL Server has the ability to perform a “compressed backup”
  
  
  
  • Since Developer Edition contains the full feature set of the Enterprise Edition, it too can perform compressed backups
  
  
• Every edition of SQL Server 2008 and later can restore a compressed backup
  
• Compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly
  
  
  
  • Please note the using backup compression increases CPU usage

Specify backup compression using the SSMS UI

When backing up the database, we can choose to compress the backup in the “Options” tab of the Backup database wizard.

Specify backup compression using the T-SQL

The backup database wizard is great, however, is not used  frequently in environments. Most of the production, staging and quality assurance environments use T-SQL statements to backup a database. Compression can be specified in the BACKUP DATABASE statement as shown below:

BACKUP DATABASE [AdventureWorks2012]

TO  DISK = N’C:DataMSSQL11.SQL2K12MSSQLBackupAdventureWorks2012.bak’

WITH NOFORMAT, NOINIT,

NAME = N’AdventureWorks2012-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Setting Backup Compression by Default

Instead of remembering to choose the backup compression, an alternative arrangement can be done wherein backup compression is always enabled by default on the given SQL Server instance. This can be done in two ways:

Backup Compression by Default setting using the SSMS UI

Within the Object Explorer, we launched the Server Instance Properties window by right-clicking on the SQL Server instance name and choosing “Properties”. When we navigate over to the “Database Settings” page, we find a switch – “Compress backup”. When checked (and the SQL Server contains an Enterprise Edition feature set), all backups using server default setting will be compressed backups.

Backup Compression by Default setting using T-SQL

Because backup compression is a SQL Server instance level configuration setting, the only way we can use T-SQL to set backup compression by default is to use the system stored procedure – sp_configure.

sp_configure ‘backup compression default’, 1

RECONFIGURE

GO

Setting the option ‘backup compression default’ to 1 will cause future backups from the server to be compressed, unless otherwise explicitly stated in the BACKUP DATABASE command.

References:

• Backup Compression: http://technet.microsoft.com/en-us/library/bb964719.aspx
  
• Backup Compression (Default) option: http://msdn.microsoft.com/en-us/library/bb677250(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.

When documenting a database, one of the first things that is done is to document the list of tables in the database and the primary and foreign keys to establish a relationship between them.

As I was gathering my thoughts one evening, I realized that I knew 7 different methods to identify primary and 6 different methods to identify foreign keys within a database (Cool!). Today, I will share these methods with you.

The methods to identify (Primary & Foreign) Keys

1. Using the system stored procedure sp_help to get the data definition information for any given table
   
   
   
   • The only issue I have with this method is that it brings back a lot of additional information
   
   
2. Use the catalog view (specifically, object catalog view): sys.key_constraints
   
   
   
   • For foreign keys, one needs to use a combination of sys.foreign_key_columns and sys.columns
   
   
3. Use the catalog view (specifically, object catalog view): sys.indexes
   
   
   
   • The reason this works is because defining a set of primary keys would, unless explicitly specified otherwise, create a clustered index on those columns
     
   • This method my not be valid for foreign keys as one may not have defined indexes on these columns
   
   
4. Use the catalog view: sys.objects
   
   
   
   • Ultimately, a key is an object, and hence, an entry for the key has to exist in the sys.objects catalog view
   
   
5. Query the INFORMATION_SCHEMA
   
   
   
   • Querying the INFORMATION_SCHEMA is recommended in environments where Microsoft SQL Server is not the only RDBMS system in the mix
     
   • Reason: INFORMATION_SCHEMA views are ISO compliant
   
   
6. Use the system stored procedure: sys.sp_pkeys or sys.sp_fkeys
   
7. Use the system stored procedure: sys.sp_primarykeys or sys.sp_foreignkeys
   
   
   
   • This method is only applicable for linked servers

7 methods to identify Primary Keys

Following is the set of scripts highlighting the 7 methods to identify Primary Keys for the table: HumanResources.Employee within the AdventureWorks2012 sample database:

--Method 01 - Get the entire table definition
USE AdventureWorks2012
GO

--Method 01 - Get the entire table definition
EXEC sp_help [HumanResources.Employee]
GO

--Method 02 - Query the catalog views (resource database)
SELECT * 
FROM sys.key_constraints AS kc 
WHERE kc.type = 'PK' 
  AND kc.is_ms_shipped = 0 
  AND kc.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 03 - Query the catalog views (resource database)
SELECT * 
FROM sys.indexes AS si 
WHERE si.type = 1 
  AND si.index_id = 1 
  AND si.is_primary_key = 1 
  AND si.object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 04 - Query the catalog views (resource database)
SELECT * 
FROM sys.objects AS so 
WHERE so.type = 'PK' 
  AND so.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 05 - Query the INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_CATALOG = DB_NAME() 
  AND CONSTRAINT_SCHEMA = 'HumanResources' 
  AND TABLE_CATALOG = DB_NAME() 
  AND TABLE_SCHEMA = 'HumanResources' 
  AND TABLE_NAME = 'Employee'
GO

--Method 06 - Use a system stored procedure
EXEC sys.sp_pkeys @table_name = 'Employee', 
                  @table_owner = 'HumanResources'
GO

----Method 07 - Works only on linked servers
--EXEC sys.sp_primarykeys @table_server = N'WIN2k8SQL2K12', 
--                        @table_name = N'Employee',
--                        @table_catalog = N'AdventureWorks2012', 
--                        @table_schema = N'HumanResources'
GO

6 methods to identify Foreign Keys

Following is the set of scripts highlighting the 6 methods to identify Primary Keys for the table: HumanResources.Employee within the AdventureWorks2012 sample database:

USE AdventureWorks2012
GO

--Method 01 - Get the entire table definition
EXEC sp_help [HumanResources.Employee]
GO

--Method 02 - Query the catalog views (resource database)
SELECT OBJECT_NAME(Fkc.constraint_object_id) AS KeyName,
       Fkc.constraint_object_id,
       Fkc.constraint_column_id,
       Fkc.parent_object_id,
       Fkc.parent_column_id,
       Fkc.referenced_object_id,
       Fkc.referenced_column_id,
       sc.name
FROM sys.foreign_key_columns AS Fkc 
INNER JOIN sys.columns AS sc ON Fkc.parent_object_id = sc.object_id
                            AND Fkc.constraint_column_id = sc.column_id
WHERE Fkc.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 03 - Query the catalog views (resource database)
SELECT * 
FROM sys.objects AS so 
WHERE so.type = 'F' 
  AND so.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 04 - Query the INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_CATALOG = DB_NAME() 
  AND CONSTRAINT_SCHEMA = 'HumanResources' 
  AND TABLE_CATALOG = DB_NAME() 
  AND TABLE_SCHEMA = 'HumanResources' 
  AND TABLE_NAME = 'Employee'
GO

--Method 05 - Use a system stored procedure
EXEC sys.sp_fkeys @pktable_name = 'Employee', 
                  @pktable_owner = 'HumanResources'
GO

----Method 06 - Works only on linked servers
--EXEC sys.sp_foreignkeys @table_server = N'WIN2k8SQL2K12', 
--                        @pktab_name = N'Employee',
--                        @pktab_catalog = N'AdventureWorks2012', 
--                        @pktab_schema = N'HumanResources'
--GO

Which method is recommended by Microsoft?

All the methods shown above are still valid (i.e. not marked as “deprecated” in SQL Server 2012). To the best of my knowledge, while there is no official documentation which explicitly states whether method A is better than method B; the KB195526 – How to Retrieve Primary Keys for SQL Server Tables recommends usage of the system stored procedure: sp_pkeys.

Which one do you use?

I would like to know which method is most used by you. Also, if there are any other methods that you use, and would like to share for the benefit of all, please do so in the comments.

References:

• KB195526 – How to Retrieve Primary Keys for SQL Server Tables
  
• System Stored Procedures:
  
  
  
  • sp_help
    
  • sp_pkeys
    
  • sp_fkeys
    
  • sp_primarykeys
    
  • sp_foreignkeys
  
  
• Catalog Views:
  
  
  
  • sys.key_constraints
    
  • sys.foreign_key_columns
    
  • sys.columns 
    
  • sys.indexes
    
  • sys.objects
  
  
• INFORMATION_SCHEMA views

Until we meet next time,

Be courteous. Drive responsibly.

Have you ever been in a situation wherein you have been working furiously on your SQL Server Management Studio (SSMS) on multiple query windows and you need to rush out to a meeting? As you know, each query editor window within the SSMS opens a new connection to the SQL Server. Open connections mean more resources being consumed at the server. Hence, when going into a meeting or lunch, one may not want to keep these connections open. I don’t.

So, what do I do in such cases?

The answer is simple: I use the “Disconnect all queries” feature available in the SSMS query editor right-click context menu. Here’s a short demo.

As you can see in the screen-shot below, I have 4 queries open – each connected to a different SQL server instance (to cycle through the open queries, simply use the Ctrl+TAB keyboard shortcut).

Now, to disconnect all queries from the corresponding SQL Server instances in one go, right-click in the query editor window, go to Connections->Disconnect all queries.

Upon clicking “Disconnect all Queries”, notice that the tabbed document spaces for all queries are now reading “not connected”.

I personally don’t like to reopen the SSMS often – it’s launched once in the morning, and the only time I close it is when I shut down my laptop in the evening.  Having a “disconnect all queries” is a cool little feature that comes in very handy.

Hope you like it as much as I do! Do you know of any cool features within SSMS? Do let me know!

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server 2012 has been around for quite a while now, and yet, I keep finding hidden enhancements. Now, the SSMS for SQL 2012 uses the VS2010 shell and therefore inherits a lot of it’s functionalities from there. Recently, I stumbled into the “Navigate To” window in the SSMS for SQL 2012 (“Denali”).

I keep all of my scripts (which I have used in the blogs or those that I use during work) under various projects within a solution. Over time as my collection grows, it becomes difficult to remember which script is located under which project, what is the name of the script, etc. In such cases, the “Navigate To” feature can used to quickly find a file within a solution in SSMS.

Some time ago, I shared three (3) scripts related to database backups and backup history on the Scripts Module of this site, BeyondRelational.com. You can quickly access these 3 scripts using the Search Module. Recently, I had to refer one of these scripts and here’s how I used the “Navigate To” window:

With the required SSMS solution open, launch the “Navigate To” window by going to the Edit menu –> Navigate To or use the keyboard shortcut [Ctrl+ ,]

Type a part of the File Name in the “Navigate To” window, and it immediately gets the list of files containing the search string.

Double-clicking the required file opens the file from the solution in the SSMS Query editor window.

So simple – I was able to reach to the required script in just a matter of seconds.

To all the DBAs out there – I find storing my scripts as solutions a very easy way to manage them – I can also share the entire solution with my colleagues via the source control system. If you have not experimented with solutions, I strongly suggest that you give it a try.

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server 2012 has been around for quite a while now, and yet, I keep finding hidden enhancements. Now, the SSMS for SQL 2012 uses the VS2010 shell and therefore inherits a lot of it’s functionalities from there. Recently, I stumbled into the “Navigate To” window in the SSMS for SQL 2012 (“Denali”).

I keep all of my scripts (which I have used in the blogs or those that I use during work) under various projects within a solution. Over time as my collection grows, it becomes difficult to remember which script is located under which project, what is the name of the script, etc. In such cases, the “Navigate To” feature can used to quickly find a file within a solution in SSMS.

Some time ago, I shared three (3) scripts related to database backups and backup history on the Scripts Module of this site, BeyondRelational.com. You can quickly access these 3 scripts using the Search Module. Recently, I had to refer one of these scripts and here’s how I used the “Navigate To” window:

With the required SSMS solution open, launch the “Navigate To” window by going to the Edit menu –> Navigate To or use the keyboard shortcut [Ctrl+ ,]

Type a part of the File Name in the “Navigate To” window, and it immediately gets the list of files containing the search string.

Double-clicking the required file opens the file from the solution in the SSMS Query editor window.

So simple – I was able to reach to the required script in just a matter of seconds.

To all the DBAs out there – I find storing my scripts as solutions a very easy way to manage them – I can also share the entire solution with my colleagues via the source control system. If you have not experimented with solutions, I strongly suggest that you give it a try.

Until we meet next time,

Be courteous. Drive responsibly.