:::: MENU ::::

Posts Categorized / DBA Interview questions

  • Aug 22 / 2009
  • 1
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Using identity columns or UniqueIdentifier for auto generated keys

The most commonly used options for key auto generation in SQL Server are

  • Using identity columns
  • Using unique key identifiers GUID

There are some differences to consider between both ways.
In case of identity columns

  • Identity columns are auto generated with each record
  • Identity columns provide uniqueness for records in a single table in which identity column is defined
  • Identity columns consume space according to data type. For example identity column defined over integer data type would consume 4 bytes

In case of Unique Identifier (GUID)

  • GUID provide uniqueness on platform. They may be generated by combination of MAC address and CPU clock.
  • GUID is required to generate for each record through NEWSEQUENTIALID() or newID() function. NEWSEQUENTIALID() GUID in serial while newID() generates random GUID.
  • GUID may consume 16 bytes of storage space on disk. It is relatively much more than 4 bytes of integer in identity column case.

So conclusion is that identity columns are efficient and suitable while working in local databases. And you have to look for GUID while working with consolidated data or data replication.
Consult BOL for further help on using GUID

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

  • Jun 09 / 2009
  • 0
DBA Interview questions, DBA thoughts, dbDigger, Professional grooming

A new DBA (need some help resources)

In my previous post i talked about the importance of sense of responsibility for new DBAs. After a DBA has took charge and now he has several tasks waiting for him. He needs reliable resources of help, from where he could get help for his tasks along with polishing the skills.
During all my career as SQL Server DBA, i have used various on line community resources and learned a lot from those.
Following is a list of resources that i keep in touch with

All above are some of many resources of SQL Server where experts are always ready to share what they know. New DBA may consult these resources for solution of any problems and also they may stay in touch with forums/blogs/articles on these forums for skill grooming.

  • Jun 06 / 2009
  • 0
kill SQL Server process
DBA Interview questions, dbDigger, Performance Tunning and Optimization

Use Kill command to terminate a SQL Server process

Some times it is required to get rid of an orphaned session or to end a session for removal of dead locks. SQL Server provides kill command for this purpose. You required System process ID (SPID) for this purpose. SPID may be get from sysprocesses or sys.sysprocesses
For example to get rid of an orphaned session with SPID 49, we have to issue following command.

Kill 49
GO

This powerful command has some restrictions associated with it. You can not kill your own process. So suicide is not permitted. Also it is not purposeful to kill the processes with following values under cmd column of sysprocesses or sys.sysprocesses.

  • AWAITING COMMAND
  • CHECKPOINT SLEEP
  • LAZY WRITER
  • LOCK MONITOR
  • SIGNAL HANDLER

Click here to read further about options associated with kill command

  • Jun 04 / 2009
  • 0
DBA Interview questions, dbDigger, System Functions

Get name of your SQL Server instance

There may be a requirement to get exact name of any SQL Server instance. There are some ways to accomplish this task. Any of these ways can be usedn>

SELECT @@servername
GO

Or system table sys.sysservers or sysservers may be queried to get the name of instance. However this table is contains one row for each server that an instance of SQL Server can access. So there may be records for linked servers in it.

  • Apr 27 / 2009
  • 2
Constraints and Keys, DBA Interview questions, dbDigger, T-SQL Interview Questions

How to update associated foreign keys when primary key specification is changed

I had to change a auto generated primary key with some meaningful key value. After designing the meaningful key it was now time to consider the impacts on foreign keys related to that specific primary key. I planned to first script all related keys for re creation after the key change operation.
In test environment i just changed the primary key through SSMS to see the impact and surprisingly SSMS asked my permission to change the related foreign keys also. I was glad and task was successfully completed with some clicks only.
To go on with an example let us choose some keys in AdventureWorks
Check primary key to be changed

USE AdventureWorks
GO

— Check Specifications of primary key to be changed
sp_help [HumanResources.Department]
GO

Specification of Primary key to be changed

We can confirm that a primary key [DepartmentID] exists in the table. Data type of [DepartmentID] is smallint. Also we may confirm that this primary key is referenced as a foreign key in table [HumanResources.EmployeeDepartmentHistory].
Now i have to update the primary key [DepartmentID] in HumanResources.Department. For this purpose

  • right click on table
  • go to design
  • In design window change data type of [DepartmentID] from smallInt to Int
  • Just as you click out of modified data type cell, you will be facing following confirmation dialog

Confirmation about data type change

Dialog wants your permission to change data types of associated foreign keys. If you click yes data types of associated foreign keys will be changed and relations will be preserved with new data type. So just save the design changes and get your changes implemented. If you click no data type of primary key will not be changed.
Now confirm the foreign key data type change

USE AdventureWorks
GO

— Check data type change in foreign key
sp_help [HumanResources.EmployeeDepartmentHistory]

In the result we may confirm the change in data type of foreign key in [HumanResources.EmployeeDepartmentHistory].
So you do not have to plan any stretigies for synchronnizing the changes made in any foreign key. SQL Server Management Studio (SSMS) designer will work for you.

Consult us to explore the Databases. Contact us