How to Create Primary Keys in SQL Server

To create a primary key

• Using SQL Server Management Studio

1. In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.

2. In Table Designer, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.

3. Right-click the row selector for the column and select Set Primary Key.

• Using Transact-SQL

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The example creates a primary key on the column .
ALTER TABLE tablename  ADD CONSTRAINT PK_ConName PRIMARY KEY CLUSTERED (column);


To create a primary key in a new table

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The example creates a table and defines a primary key on the column .

CREATE TABLE tablename ( columnName datatype NOT NULL, CONSTRAINT PK_ConName PRIMARY KEY CLUSTERED (column) ); 

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.

How to Specify Default Values for Columns

To specify a default value for a column

• Using SQL Server Management Studio

1. In Object Explorer, right-click the table with columns for which you want to change the scale and click Design.

2. Select the column for which you want to specify a default value.

3. In the Column Properties tab, enter the new default value in the Default Value or Binding property.

4. On the File menu, click Save table name.

• Using Transact-SQL

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

ALTER TABLE tablename ADD CONSTRAINT Con_Name DEFAULT 50 FOR columnName ;

NOTE:

1. To enter a text string, enclose the value in single quotation marks ('); do not use double quotation marks (") because they are reserved for quoted identifiers.

2. To enter a numeric default, enter the number without quotation marks around it.

3. To enter an object/function, enter the name of the object/function without quotation marks around it.

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.

How to Rename Tables in SQL Server

You can rename a table in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

• Using SQL Server Management Studio

1. To rename a table,  In Object Explorer, right-click the table you want to rename and choose Design from the   shortcut menu.

2. From the View menu, choose Properties.

3. In the field for the Name value in the Properties window, type a new name for the table.

4. To cancel this action, press the ESC key before leaving this field.

5. From the File menu choose Save table name.

• Using Transact-SQL

1. To rename a table In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. The following example renames the "login" table to "Users" in the  schema. Copy and paste the following example into the query window and click Execute.

                         

                           EXEC sp_rename 'login', 'Users';

(Note: make sure that you have selected correct database to which table to be rename is belong)

Limitations and Restrictions

Renaming a table will not automatically rename references to that table. You must manually modify any objects that reference the renamed table. For example, if you rename a table and that table is referenced in a trigger, you must modify the trigger to reflect the new table name. 

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.

Change Column Order in a Table SQL Server

This task cannot be performed using Transact-SQL statements.

Using SQL Server Management Studio

To change the column order


1. In Object Explorer, right-click the table with columns you want to reorder and click Design.


2. Select the box to the left of the column name that you want to reorder.


3. Drag the column to another location within the table.

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.

Delete Tables (Database Engine) from SQL Server Database

You can delete (drop) a table from your database in SQL Server by using SQL Server Management Studio or Transact-SQL.

1. Using SQL Server Management Studio

1.To delete a table from the database

2. In Object Explorer, select the table you want to delete.

3. Right-click the table and choose Delete from the shortcut menu.

4. A message box prompts you to confirm the deletion. Click Yes.

2.Using Transact-SQL

1. To delete a table in Query Editor

2. In Object Explorer, connect to an instance of Database Engine.

3. On the Standard bar, click New Query.

Write following query in query window and click Execute.

DROP TABLE dbo.tablename;

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.

Steps to Create Tables in SQL Server

You can create a new table, name it, and add it to an existing database in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

1. Using SQL Server Management Studio

To create a table with Table Designer

1. In Object Explorer, connect to the instance of Database Engine that contains the database to be modified.
2. In Object Explorer, expand the Databases node and then expand the database that will contain the new table.
3. In Object Explorer, right-click the Tables node of your database and then click New Table.
4. Type column names, choose data types, and choose whether to allow nulls for each column as shown in the following illustration.
   
5. To specify more properties for a column, such as identity or computed column values, click the column and in the column properties tab, choose the appropriate properties.
6. To specify a column as a primary key, right-click the column and select Set Primary Key. 
7. To create foreign key relationships, check constraints, or indexes, right-click in the Table Designer pane and select an object from the list as shown in the following illustration.
   
   
   
8. By default, the table is contained in the dbo schema. To specify a different schema for the table, right-click in the Table Designer pane and select Properties as shown in the following illustration. From the Schema drop-down list, select the appropriate schema.
   
   
   
9. From the File menu, choose Save table name.
10. In the Choose Name dialog box, type a name for the table and click OK.
11. To view the new table, in Object Explorer, expand the Tables node and press F5 to refresh the list of objects. The new table is displayed in the list of tables.

2.Using Transact-SQL

To create a table in the Query Editor

1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute.
   CREATE TABLE dbo.PurchaseOrderDetail
   (
       ID int NOT NULL
       ,LNumber smallint NOT NULL
       ,ProductID int NULL
       ,Price money NULL
       ,OrderQty smallint NULL
       ,ReceivedQty float NULL
       ,RejectedQty float NULL
       ,DueDate datetime NULL
   );

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.

Creating, Altering or Dropping a database in SQL

A SQL Server database can be created, altered and dropped by two ways

1. Graphically using SQL Server Management Studio (SSMS) or
2. Using a Query

To create the database graphically
1. Right Click on Databases folder in the Object explorer
2. Select New Database
3. In the New Database dialog box, enter the Database name and click OK.


To Create the database using a query
Create database DatabaseName


Whether, you create a database graphically using the designer or, using a query, the following 2 files gets generated.
.MDF file - Data File (Contains actual data)
.LDF file - Transaction Log file (Used to recover the database)


To alter a database, once it's created 
Alter database DatabaseName Modify Name = NewDatabaseName


Alternatively, you can also use system stored procedure
Execute sp_renameDB 'OldDatabaseName','NewDatabaseName'


To Delete or Drop a database
Drop Database DatabaseThatYouWantToDrop


Dropping a database, deletes the LDF and MDF files.


You cannot drop a database, if it is currently in use. You get an error stating - Cannot drop database "NewDatabaseName" because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.
Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate

With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.


Note: System databases cannot be dropped.

Friends, If you like this post, please share with your friends on Facebook and Google+ and recommend us on Google using the g+1 button on the top right hand corner.