:::: MENU ::::

Posts Categorized / SSMS tips and tricks

  • Jun 22 / 2009
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, SSMS tips and tricks, T-SQL Scripts

Create unique key constraint through T-SQL and SSMS

There may be required to make sure that to fulfill a certain business logic, each record should be unique in terms of some columns. To implement this there is no need to create any primary key having combination of columns. Simply a unique key constraint may be created.
To consider an example of creating unique key constraint let us create a sample table

 
USE AdventureWorks  
 GO  
 CREATE TABLE TestUKey(serNumb SMALLINT IDENTITY(1,1),   
 name VARCHAR(50),  
 city VARCHAR(50),  
 phone VARCHAR(13),  
 cellNumb VARCHAR(13))  
 GO  

Suppose now it is required to make sure that each record have unique combined values in name and cellnumb. To implement this constraint we may use following T-SQL statement
[/sql] USE AdventureWorks
GO
ALTER TABLE TestUKey
ADD CONSTRAINT IX_TestUKey_NameCell
UNIQUE(Name, CellNumb)
GO
[/sql]
Our required logic has been implemented and to confirm it following is a part of result from <

sp_help TestUKey

Unique key constraint confirmation

To create unique key constraint through SQL Server Management Studio (SSMS), following are required steps.
Drop existing unique key constraint so that we may create unique key constraint through SSMS

 
USE AdventureWorks  
 GO  
 ALTER TABLE TestUKey  
 DROP CONSTRAINT IX_TestUKey_NameCell  
 GO  

Now we are ready to create unique key constraint through SSMS

  • Right click on table on which constraint will be implemented and go to Design
  • Right click on left bar of design to access indexes and keys or access from tool bar

Create unique key constraint through SSMS 1

  • A frame will appear. Choose both columns and select unique key in type.
  • Close the frame and exit from designer.

Unique key has been created and this may be verified through SSMS

Confirm unique key constraint through SSMS

I have noticed that along with each unique key created a non clustered index is automatically created comprising the columns of unique key.
Click here for another example of unique key constraint implementation.

  • Jun 11 / 2009
  • 0
DBA Interview questions, dbDigger, SSMS tips and tricks

Can SQL Server management studio (SSMS) be used with other versions of SQL Server

Can SQL Server management studio (SSMS) be used with other versions of SQL Server. Although a very basic but important questions for new users of SQL Server. It always amazed me that you can connect any version of SQL Server management studio (SSMS) to any of previous version instance of SQL Server. I used SSMS 2005 for a long time to work with instance of SQL Server 2000. And currently i am using SSMS 2008 to work with instances of SQL Server 2000/2005 and 2008.
But you can not use SSMS 2005 to connect and work with SQL Server 2008 instance. Hence main rule is that SSMS can connect to instances of its current version and previous versions. It can not connect to SQL Server instances that are later than version of SSMS.

  • Apr 29 / 2009
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, Publications of Atif Shehzad on MSSQLTips.com, SSMS tips and tricks

Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted)

My article related to Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted) is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Controlling changes in SQL Server 2008 that require table re-creation (saving changes is not permitted

  • Apr 21 / 2009
  • 0
dbDigger, SSMS tips and tricks

Use filter for objects in SQL Srever Management Studio (SSMS)

SQL Server Management Studio (SSMS) provides very useful facility while working with large number of objects. We may apply a filter on folder of required objects and instead of retrieval of all objects under that folder filtered objects according to names will be shown under that folder and also in Object Explorer Details frame. In SSMS 2005 filtration is allowed upon Name, Schema, and Creation Date. While in SSMS 2008 filtration on owner of object can also be applied.
Right click on folder of required objects and go to Filter -> Filter Settings

Filter settings in SSMS

Now in order to get all tables having string ‘product’ in their names, i will provide ‘product’ in name filed of filter.

Apply filter in SSMS

After pressing OK button, i will get filtered tables under the folder and also in Object Explorer Details frame.

Filtered Tables in SSMS

Similarly filters may be applied upon Name, Schema, and Creation Date in SSMS 2008. In SSMS 2008 along with these fields filters may also be applied upon owner.
For removal of filter just go to same menu and there Remove filter option will be enabled to work for you.

Remove filters in SSMS

For any change in filter setting you may go to Filter Settings frame. Following are objects where you can apply filters

  • tables
  • views
  • stored procedures
  • functions
  • Log Ins
  • jobs
  • Apr 21 / 2009
  • 0
dbDigger, SSMS tips and tricks

Select multiple objects in SQL Srever Management Studio (SSMS)

I have noticed some people that while working in SQL Server Management Studio (SSMS), they try to select multiple objects in object explorer. Multiple selection of objects by holding Cntrl key will not work in object explorer of SSMS. Multiple selection of objects feature is present in SSMS but for that you have to select the objects in Object Explorer Details frame. Just hold Cntrl key and click each object that is required to select.

Select multiple objects in SSMS

Multiple selection of objects in SSMS may be used for deletion of multiple objects through SSMS. Or scripts for creation/drop of multiple objects may be generated by using multiple selection feature in Object Explorer Details frame.

Consult us to explore the Databases. Contact us