Creating Primary Key in a SQL Server table

In this blog we shall learn how to create a Primary Key on a table using T-SQL.

A Primary Key on a table can be either created at the time of creating the table or after the table is created by altering the table and adding the Primary Key Constraint.  This blog covers the first method of creating the table and Primary Key at the same time. The important change in the create table script is to include a section inside the table schema definition by mentioning the keyword CONTRAINT followed by the Primary Key Name followed by the Key words PRIMARY KEY.   

CREATE TABLE EmployeeTable ( EmployeeID INT , FName VARCHAR (25)
, LName VARCHAR (25), Gender VARCHAR (1),
CONSTRAINT [PK_EMP_EMPID] PRIMARY KEY
( EmployeeID ASC ) ) ON [PRIMARY]  GO

Some points to remember about Primary Keys

  • A table can consist of one and only one Primary Key.
  • By default a clustered Primary Key is created.
  • A Primary Key on a table can be based on one or more columns of that table.
  • A Primary Key ensure no duplicate values can exist in that column(s).
  • A Primary Key on one column can be used as a parent key on another table and helps in ensuring referential integrity between those tables.
  • A Primary Key is required when creating a Transactional Publication in Replication

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

DB_NAME() System Function in SQL Server

In this blog, let’s take a look at a SQL Server System function, DB_NAME() .

This function is pretty handy when writing scripts that need you to identify the the Database name based on Database ID.

DB_NAME()

DB_NAME() function accepts an optional integer parameter (database_id) and returns the database name of that database_id, if no parameter is mentioned it returns the database name of the current database in whose context the script session is being executed. Let’s look at examples to understand it.

In the following picure, the function DB_NAME() returns the name of the database whose database_id is 1. Since the parameter value is passed on it returns the database name of that database_id, even though the query is running in the database SampleDataBase.

In the next example we look at how this function behaves when the optional parameter is not passed.

 

The result in this query is the database name in which the  query is executed. It returns the database name under whose contect the query is run, when there is a requirement to capture the database name through script, this can be used.

You may also want to take a look at this blog which explains about renaming the Database and the Database objects using T-SQL command. http://learnsqlwithbru.com/2010/03/01/renaming-database-and-database-objects-using-t-sql/

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Find Databases without recent full backups

One of the important tasks a SQL Server DBA does is database backup. While maintaining the backups of the important database is important, the need to monitor that the exists a valid Full backups for the databases is equally important. This blog will try to explain how to find the list of databases which do not have a Full backup in the recent few days. (8 days in this example). You can obviously change the value in DATEADD function to suit your choice for # of days to look for.

The code used in this example is found at the end of this blog.

First, I run the script to find the list of databases which do not have a full backup. The very first statement you see in the script (SELECT @@VERSION ) is not required, I included it my example to show the SQL Server Version on which I am executing these scripts. The reason for including it is, a slight change in the script. In SQL Server 2005 and 2008, I am querying sys.databases and in SQL Server 2000 it does not exist, so I would need to use master..sysdatabases.

The query returns list of the databases on my instance which do not have a full backup in the last 8 days.

I then perform a backup of Adventureworks database, which is one of the database missing a full backup. the picture below is the script I run to create a backup of Adventureworks DB.

The next thing I do is run the same script that was run at the beginning to find out the databases without full backups and this time displays only the database Snapshot_Source.

The following is the script that works for SQL Server 2000. Note there is no database missing full backups, indicating I have at least full backup of all databases on that server.. Based on the importance of the data in the databases, other backups types have to be configured and monitored. However, a full database backup would always be the base of any database recovery process and should be as recent as possible to avoid delays in recovery process.

Script used in this example…

SELECT @@VERSION AS [SQL SERVER VERSION]

SELECT NAME FROM sys.databases
WHERE NAME != 'TEMPDB'
AND NAME NOT IN (    SELECT DISTINCT database_name FROM msdb..backupset
WHERE backup_start_date > DATEADD(DAY,-8,GETDATE())
AND  TYPE = 'D' )

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty