Feeds:
Posts
Comments

Archive for September, 2013

SQL Server – How to Add More Than One Column as a Primary Key

Posted in Constraints, SSMS, tagged , , , , on September 17, 2013| 2 Comments »

Sometimes you might want to add more than one column as primary key. For example, if you have three columns named Last Name, First Name and Address and  there can be duplicate Last Names  or duplicate First Names but can never have duplicates in Last Name, First Name  and Address combined together

Adding columns to a primary key constraint using T-SQL script:

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID 
PRIMARY KEY CLUSTERED (TransactionID,[ProductID]);
GO

In order to add columns to already existing primary key, you need to drop the primary key constraint first to add columns later.
Drop primary key script is provided below.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive 
DROP constraint [PK_TransactionHistoryArchive_TransactionID]

Adding columns to a primary key constraint using SSMS GUI:

Right click on the Table Name and click on ‘Design’. Hold Ctrl key and select the column names that you want to add as Primary key. Then click on the ‘Set Primary Key’ as shown below.

1

The below screen shot shows three columns as primary key.

2

As a quick fact, the maximum number of columns that you can add to the primary key is limited to 16.

Read Full Post »

SQL Server – How to Create Registered Servers to Easily Manage and Query Multiple Instances at a Time

Posted in Registered Servers, SSMS, tagged , , on September 16, 2013| 1 Comment »

Registered servers is a great way to manage, categorize and access the SQL servers through SSMS and also one of the overlooked aspects. Registered servers are handy when you want to have all your servers at one place, sorted and categorized in various ways. With registered servers all your instances are just a click away eliminating the need to type the instance name every time you connect. Apart from the aforementioned uses of registered server I have put down other helpful scenarios where registered servers come in handy.

  • Easy to share among the team members and which is especially helpful for a newly hired DBA with import and export feature.
  • Does great job when you want to run a query on multiple servers in one query window. Either you want to check backups for all your production   instances or disable a built-in admin login from all your 2005 servers.
  • Helps to immediately identify servers’ availability after an outage.

Here are the steps to register individual servers using GUI

From the SSMS, open view and click on registered servers or Ctrl+Alt+G if you are feeling lucky 🙂

Reg ser 1

You will have an option of creating either a new group or a new server. Here I have chosen ‘New Server Registration’

Reg ser 2

As you can see here, server name is the actual name/ip address and Registered server name is what you like it to display.

Reg ser 3

I usually prefer the servers categorized based on the environment as shown below.

Reg ser 4

You can right click on any of the groups and open a new query window. That means, a query window is open to execute a query that gets the result from all the servers. The below screenshot shows the number of servers connected. This comes handy when you are checking the servers for connection issues.

Reg ser 5

Sure, there are hundreds of servers in an organization and adding a server one at a time is probably not a best way to go. This hints us to a way to add multiple entries at a time. Instead of reinventing the wheel again I found a great way to do this HERE

Technical Reviewer(s): Venkata Suresh Raavi; Jaipal Vajrala

Read Full Post »

SQL Server – Find all the LOB Data Type Columns in a Database Using T-SQL Script

Posted in LOB, Queries, T-SQL, tagged , , , on September 12, 2013| 1 Comment »

Today morning when I was working on a huge database containing lots of LOB data, I was required to know what tables have LOB data, and the list of LOB columns by table name along with the data type.

I found that starting SQL Server 2005, we can easily retrieve this information from the Information Schema Views, by specifying “COLUMN_NAME” in view_name. There may be even a better way to do this, but here is what I came-up with.

USE [AdventureWorks2012]
GO
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE IN ('FILESTREAM','XML','VARBINARY','TEXT','NTEXT','IMAGE') 
OR(DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH = -1)
ORDER BY TABLE_NAME

HERE is an explanation by Pinal Dave, why I have to include CHARACTER_MAXIMUM_LENGTH = -1 in the above query

Sample Output:

Untitled

Below column types are considered LOB (Large Objects):
VARCHAR(MAX), NVARCHAR(MAX), FILESTREAM, XML, VARBINARY,
TEXT, NTEXT, IMAGE

Note: Data types TEXT, NTEXT and IMAGE were deprecated in SQL Server 2012 and are going to be removed in future versions, so Microsoft doesn’t recommend using them in new applications. For more information, please refer HERE

Read Full Post »

SQL Server – How to Perform Striped Database Backup & Restore Using T-SQL Command

Posted in Backup, Disk Space, T-SQL, Transaction Log, tagged , , , , on September 3, 2013| 1 Comment »

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB).

As shown in the picture below, striping is nothing but splitting one backup file to multiple backup files (maximum 64 files). However, these files may or may not be the same size (depends on the storage disks IO).

Striped Backups

By Striping a backup we can:

  • Increase backup throughput and reduce the backup time window
  • Allow backups & restores to be written or to be read from all devices in parallel
  • Enable backup to different disks, thus distribute the space usage

Below are the simple T-SQL backup commands using AdventureWorks2012 sample database as an example.

T-SQL command for Striping a database backup

Note: In the below script, I used only Disk C to contain all the striped .bak files. However, we can  direct to multiple disks if required

-- Striped Backups -- Backup to multiple files - 4 files in this case
BACKUP DATABASE [AdventureWorks2012]
TO 
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL command to restore from Striped database backup 

--Restoring from striped backup -- from multiple files
RESTORE DATABASE [AdventureWorks2012] 
FROM  
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO

Also, we can apply the same striping concept on Log backups. Below is how we do it

T-SQL command for Striping transaction log backup

--Striped Log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn', 
DISK='C:\AdventureWorks2012_2.trn', 
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

Demo: Normal Backup Vs Striped Backup

Below are the results and screenshots from a live production environemnt. This once again proves striping backup files increase data transfer rate and reduce the time to backup

Results:

Backup Type Time to Backup Data Transfer Rate
Normal Backup (1 File) 537.6 Seconds 111.9 MB/Sec
Striped Backup (4 Files) 201.0 Seconds 299.3 MB/Sec

Screenshots:

StripedBackupDemo_1of2

StripedBackupDemo_2of2

However, the major downside of striping a backup is that if at-least one backup file is corrupt, restore operation cannot be performed using the other files.

Also, HERE is why I haven’t discussed striping backups using SSMS GUI.

Technical Reviewer(s): Hareesh Gottipati; Jaipal Vajrala

Read Full Post »