SQL SERVER – How to Drop Primary Key Contraint from a Table

First, we will create a table that has primary key. Next, we will drop the primary key successfully using the correct syntax of SQL Server.

CREATE TABLE Table1(
Col1 INT NOT NULL,
Col2 VARCHAR(100)
CONSTRAINT PK_Table1_Col1 PRIMARY KEY CLUSTERED (
Col1 ASC)
)
GO

Second, try to drop the primary key constraint using following lines.

/* For SQL Server/Oracle/MS ACCESS */
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

/* For MySql */
ALTER TABLE Table1
DROP PRIMARY KEY
GO

Use the tips!!!

Read more

SQL Interview Tips - 1

What is data integrity?

• Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid.
  
• also acts as a trap for otherwise undetectable bugs within applications.

Explain constraints?
Candidate key :

• A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data.
• Each table may have one or more candidate keys.
• One of these candidate keys is selected as the table primary key.

Primary Key :

• Primary key will create column data uniqueness in the table.
• Primary key will create clustered index by default
• Only one Primay key can be created for a table
• Multiple columns can be consolidated to form a single primary key
• It won't allow null values.

Foreign key :

• A foreign key is a field in a relational table that matches the primary key column of another table.
• The foreign key can be used to cross-reference tables.
• The foreign key is used to prevent actions that would destroy link between tables.
• The foreign key also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Unique Key :

• Unique key constraint will provide you a constraint like the column values should retain uniqueness.
• It will allow null value in the column.
• It will create non-clustered index by default
• Any number of unique constraints can be added to a table.

Surrogate Key / Artificial Key / Identity key:

• The value is unique system-wide, hence never reused
• The value is system generated
• The value is not manipulable by the user or application
• The value contains no semantic meaning
• The value is not visible to the user or application
• Surrogate Key is the solution for critical column problems. For example, the customur purchases different items in differnt locations,for this situation we have to maintain historical data.
• By using surrogate key we can introduce the row in the datawarehouse to maintain historical data.
• Surrogate is mainly used in slowely changing dimensions,it mantaining the uniqueness in the table.it is used to track the old value with the new one.
• We can say "Surrogate key" is a User defined primary key

What's the difference between a primary key and a unique key?

• Primary key wont allow nulls, unique key allow nulls.
• Primary key constraints are more restrictive than Unique constraints
• Unique constraints create unique non-clustered indexes by default;Pprimary key constraints create unique clustered indexes by default.
• There can be only one clustered index on a table, so you can specify only one unique clustered or primary key clustered constraint.

What is the difference between a primary key and a surrogate key?

• A primary key is a special constraint on a column or set of columns.
  
• A primary key constraint ensures that the column(s) so designated have no NULL values, and that every value is unique.
  
• Physically, a primary key is implemented by the database system using a unique index, and all the columns in the primary key must have been declared NOT NULL.
  
• A table may have only one primary key, but it may be composite (consist of more than one column).
• A surrogate key is any column or set of columns that can be declared as the primary key instead of a "real" or natural key.
  
• Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key.
  
• A table could actually have more than one surrogate key, although this would be unusual.
  
• The most common type of surrogate key is an incrementing integer, such as an auto_increment column in MySQL, or a sequence in Oracle, or an identity column in SQL Server.

Pros and Cons of Surrogate Key :

Pros:

• Business Logic is not in the keys.
• Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
• Joins are very fast.
• No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached - very scalable.

Cons :

• An additional index is needed. In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
• Cannot be used as a search key.
• If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
• Always requires a join when browsing the child table(s).

Pros and Cons of Natural Key :

Pros :

1. No additional Index.
2. Can be used as a search key.

Cons:

1. If not chosen wisely (business meaning in the key(s)), then over a period of time additions may be required to the PK and modifications to the PK can occur.
2. If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well. Since storage is more, less data-values get stored per index page. Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.
3. Locking contentions can arise if using application driven generation mechanism for the key.
4. Can’t enter a record until value is known since the value has some meaning.

Use the tips!!!

Read more

Convert string data into DataTime in Sql Server

In SQL Server, no direct funtion to convert interger string into time format. We need to do it by our own logic. See the below sample which convert integer string to datetime.

DECLARE 

      @DateTimeValue varchar(30), 

      @DateValue char(8), 

      @TimeValue char(6)

 

SELECT 

      @DateValue = '20080723', 

      @TimeValue = '211957' 

 

SELECT @DateTimeValue = 

       convert(varchar, convert(datetime, @DateValue), 111) 

    + ' ' + substring(@TimeValue, 1, 2) 

    + ':' + substring(@TimeValue, 3, 2) 

    + ':' + substring(@TimeValue, 5, 2) 

 

SELECT 

    DateInput = @DateValue, 

    TimeInput = @TimeValue, 

    DateTimeOutput = @DateTimeValue

Use this tips!!!

Read more

Adding Northwind Pub Database to SQL Server 2005

SQL Server 2005 doesn't include the Pubs and Northwind databases.

• You can click here to download (.msi) the latest version of Pub and Northwind sample databases .
• Install .msi file. It will create binary files (.mdf , .ldf) and SQL scripts (.sql) files.

Installing sample databases from the Management Studio GUI:

• This method will use binary files.
• Copy .mdf and .ldf files back to SQL DATA folder like c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\. This path varies somewhat depending how many instances of SQL Server you have on your machine.
• Open SQL Server 2005.
• Right-click the root database
• Click "Attach"
• Click the "Add" button and choose the *.mdf file from the \Data folder
• Click OK

Installing sample databases from the command line:

• This method will use SQL scripts files.
• Click the "New Query" toolbar button
• Cut and paste the contents of the instnwnd.sql or instpubs.sql scripts in the query window
• Hit F5 button to run.

Use the technology!!!

Read more

Open Enterprise Manager using keyboard shortcut

To open enterprise manager in SQL Server 2000 using keyboard shortcut: Microsoft Management Console (MMC)

• Start menu, Run, type "mmc"
• In the file menu, recent file list select "SQL Server Enterprise Manager.msc"

If list is not there then try this one

• Select Start - Run.
• At the Open prompt enter: mmc
• Click OK
• Select File - Add/Remove Snap-in...
• Click Add...
• Select Microsoft SQL Enterprise Manager
• Click Add, then Close
• Click Ok to return to the mmc.
• Select File - Save As...
• Delete or rename the original (offending) file out the way.
• Save the new msc file as C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC

or

• In the start menu, Microsoft SQL Server, Enterprise Manager, right click on the menu, select properties
• It will open properties for the shortcut
• In the shortcut key, specify whatever shortcut key-combination you want.

For more information click here .

Use the technology!!!

Read more

Job scheduler in SQL Server 2000

Job scheduler in SQL Server 2000

To create a SQL job scheduler in Sql Server 2000 and run it automatically in a particular interval. For this, we need to follow the steps given below:

1. First we create one test database called 'KannanOrganization' in Sql Server 2000.
2. Then we create one test table namely 'Employee' with the following table schema.
   

   Table Name : Employee
   
   -----------------------------------------------------------------------
   
   Column Name                  DataType         Width    Dec   Allow Null
   
   ------------------------------------------------------------------------
   Id                           INT              4                 No, PK , Identity
   
   FirstName                    VARCHAR          50                Yes
   LastName                     VARCHAR          50                Yes
   Salary                       DECIMAL          8          0      Yes
   ------------------------------------------------------------------------
   See the below snaps for more information.
   
   
   

3. Then we need to create one store procedure for auto Sql Jjob test. For a sample, here I created one stored procedure called, 'InsertEmplyee' for testing SQL job.
   

   CREATE PROCEDURE [dbo].[InsertEmployee]
   AS
             INSERT Employee
                       (FirstName, LastName, Salary)
             VALUES
                       ('Kannan', 'Arjun', 17658.50)
   GO
   
   Now stored procedure is ready to use.
   
   
   

4. Next we need to create a job scheduler. First we select Management ---> SQL Server Agent --> Job from Sql server you connected currently. See the snaps for clear picture.
   
   
5. Right click and select the New Job from the from Job options. It will open New Job Property window. Fill the relavent information in this window. Like this snaps,
   
   


6. Next, we goto Step tab and select New button from it. It will open New Job Step window. Fill the query type, database, stored procedure information like the below snaps.
   
   
   Step Name - Name of the Step. We may use multiple step in single job.
   Type - Select the type of the query to be executed. In out case, 'TSQL'
   Database - Select your database. Here for testing select 'KannanOrganization'.
   Commend - fields is going to execute our stored procedure.
   
   
7. Once we finished our Step tab works then we go for Scheduler setting in Schduler tab.
   Here it providing 4 verities of scheduler for our job running. We can select the type which you want. For my case, I need to run my job on every 1 mins. So that I can select option number 4 Recurring.
   
   For date and time setting, select Change button in the same tab. It will open another window for our date and time settings. See the snaps below.
   
   


8. Once we fixed our date and time setting, click ok button. It will create our job successfully. Now the time to start our job manully by right click the job we created and select Start Job option from that. See the snaps.
   


9. After running our job, we see the job result by open out Employee table and see the records inserted. See the snaps.
   
   
   It will run and insert the record at every 1 mins. In the same way we can do our requirement.

Use the technology!!!

Read more