Archive for the ‘SSMS’ Category

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;
ALTER TABLE Production.TransactionHistoryArchive 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID 
PRIMARY KEY CLUSTERED (TransactionID,[ProductID]);

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;
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.


The below screen shot shows three columns as primary key.


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


Read Full Post »

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 »