How to rename the existing table in SQL server?

After the table creation ,it is also possible to change the table name in SQL server.

Syntax : 

SP_RENAME '[EXISTING TABLE NAME]','[NEW TABLE NAME]'

Example :

SP_RENAME 'Student','StudentDetails'

Now the table name will be changed from Student to StudentDetails in the executed Database.

How to rename the existing column in SQL server?

After the Column creation ,it is also possible to change the column name in SQL server.

Syntax : 

SP_RENAME '[TABLE NAME].[EXISTING COLUMN NAME]','[NEW COLUMN NAME]','COLUMN'

Example :

SP_RENAME 'Student.StudentAddress','StudentAdd','Column'

Now the Column name will be changed from StudentAddress to StudentAdd in Student table.

How to add a new column in SQL server to the existing table ?

After the table creation,we can also add the new column to the exiting table using the ALTER TABLE keyword.

Syntax  :

ALTER TABLE [TABLE NAME] ADD  [COLUMN NAME]   [DATA TYPE] [NULL | NOT NULL CONSTRAINT]

Example :

ALTER TABLE Student ADD StudentAddress  VARCHAR(100) NULL

How to create the primary key in SQL server after table creation ?

If we forget to create the primary key while table creation,we  also have the option to add the primary key for the existing table.

We can add the primary for the existing table using ALTER TABLE keyword.

Syntax  :

ALTER TABLE [TABLE NAME] ADD CONSTRAINT [PRIMARY KEY NAME] PRIMARY KEY  ([COLUMN NAME] ASC)

Example :

ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY ([StudentID] ASC)

Explanation: 

PK_Student --> Refers to primary key name.

StudentID -- > Refers to the primary key column name.

Now the primary key is created successfully in the existing table using T-SQL query.

What is primary key in a table and How to create it ?

Primary key is one of the constraints in SQL server which enforces the Unique value for each row in a table.

A table can have only one primary key.The column which act as a primary key will not allow null values.Usually the primary key columns are defined on identity columns.

Create Syntax : 

CREATE TABLE [TABLENAME]

(

    [COLUMNNAME] [DATETYPE] [CONSTRAINT],

    [COLUMNNAME1] [DATETYPE] [CONSTRAINT],

    .

    .

    .

    [COLUMNNAME(N)] [DATETYPE] [CONSTRAINT],

)

Example :

CREATE TABLE dbo.Department

(

    [ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [NAME] VARCHAR(10) NULL, 

    [AGE] [INT] NOT NULL

)

If we use the above method then the primary key will be created by the database engine.

If you want to create the primary key on user defined name ,then use the below method.

Example :

CREATE TABLE dbo.Department

(

    [ID] [INT] IDENTITY(1,1) NOT NULL ,

    [NAME] VARCHAR(10) NULL, 

    [AGE] [INT] NOT NULL,

    CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([ID]  ASC)    

)

Now the primary key will be created in the name of PK_ID.

Now we created the constraint in ASC order,so it will always order the data in ascending order.

How to create a table in SQL server with Management studio ?

Here are the few steps to create a new table in SQL server.

Step 1 :

Right click the tables folder and click the new table option.

 Step 2 : 

Enter the Column Name ,datatype and Allow Nulls columns.

If the allow Nulls column is checked then it will allow Null value,else it will not allow null value.

Step 3 : 

In Column properties (Highlighted in Red colour) ,we can change the identity Specification to specify the start value and seed value.Initially the identity specification will be NO.

Step 4 :

Now I changed the identity specification value as YES.Identity increment is changed as 1 and identity seed is changed as 1.

Now the EmployeeID column starting value will be 1 and increment value will be 1.

Step 5 :

Now we need to save the table using File ---> Save (or) Ctrl+S command.Now the Choose name popup will appear.Once the table name is entered click OK to save the table.

Step 6 :

Now we can see the new table "Employee"created in the tables folder.

Step 7 :

Expand the table name and You can see the columns which we created in step 2.

How to Create a table in sql server ?

The table can be created in the database using the following syntax.

Create Syntax :

CREATE TABLE  [SCHEMA NAME] [TABLE NAME]

(

   [COLUMN NAME1] [DATA TYPE] [CONSTRAINT],

   [COLUMN NAME2] [DATA TYPE] [CONSTRAINT],

   .

   .

   .

   .

 [COLUMN NAME(N)] [DATA TYPE] [CONSTRAINT],

)

Example :

CREATE TABLE DBO.EMPLOYEE

(

   [EmployeeID] [INT] IDENTITY (1,2) NOT NULL,

   [ForeName] [NVARHCAR] (50) NOT NULL,

   [SurName] [NVARHCAR] (50) NULL,

   [EmployeeDep] [NVARHCAR] (50) NOT NULL,

   [Salary] [FLOAT] NOT NULL

)

Now we have created a new table called Employee.

• EmployeeID is int column and not nullable.
• ForeName is string column and not nullable.
• SurName is string column and nullable
• EmployeeDep is string column and not nullable
• Salary is float column and not nullable.

Identity (1,2) refers to start value and seed value.

Here 1 refers to the starting value of the EmployeeID column.

2 refers to the incremental value of the EmployeeID column.

So the EmployeeID column first value will be 1 and next value will be 3 and 5 and goes on..

We can  also change the start value and seed value.(Eg : Identity(1000,1)).So the starting value will be 1000 and next value will be 1001,1002....(n).

Note : 

Nullable column will allow Null value.

Not Nullable column will not allow Null value.

How to use the user defined datatype in Table creation ?

In SQL server ,while table creation we can use the SQL server datatype to create columns and also possible to use our own user data type.

If you are going to use the same data type with same length for your entire project ,then we can create our own user defined datatype and use it .

Now we will use our created datatype which is created in our previous post (Refer Here).

Create Table dbo.Student 

(

       StudentID Int Not Null,

       Name Varchar(50) Not Null,

       Department UserType 

)

Now we have used our created datatype in the above table. So the user datatype will be very helpful in this kind of scenario.

What is user defined datatype in sql server and how to create ?

In SQL server ,we can create our own userdatatype and can be used while table creation.

Create Syntax :

CREATE TYPE [TYPE NAME] FROM [DATE TYPE] [NULL | NOT NULL CONSTRAINT]

Example:

CREATE TYPE  UserType FROM NVARCHAR(10) NULL

Now the type named "UserType" is created as NVARCHAR datatype and it is nullable column.

Drop Syntax :

DROP TYPE [TYPE NAME]

DROP TYPE UserType

we can also add the type using System SP.

Create Syntax :

EXEC SP_ADDTYPE [TYPE NAME],[DATA TYPE(LENGTH)],[NULL | NOT NULL CONSTRAINT]

Example:

Exec Sp_AddType 'UserType','Nvarchar(10)','Null'

Its also possible to alter the type using the following syntax.

Alter Syntax : 

EXEC SP_RENAME [EXISTING TYPE NAME],[NEW TYPE NAME],[USERDATETYPE]

Example: 

Exec Sp_Rename 'UserType','UserTypeName','USERDATATYPE'

List of Datatypes in SQL server with Precedence

In SQL server all the datatypes are executed based on the precedence.

The datatype with highest precedence will be executed first and with lower precedence will be executed next.

Please find the list of datatypes used in sql server.

1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

What is table in sql server?

• Table is a database object which can be used to store the data.
• Table is a combination of rows and columns
• The columns are created during the table creation.
• Columns can also be added once the table is created.
• Rows are also called as Records.

How to create a database in Management studio ?

STEP 1 : 

Right click the database and select New Database option to create a new database in management studio.Refer the screenshot below.

STEP 2 : 

In the New database window ,enter the database name in text-box.I have created a database name as Sample.You can see the logical name created in the name of database such as sample and sample_log.Refer the screenshot below.

Here Logical name Sample is MasterDataFile and Sample_Log is LogDataFile.

The logical name can be changed by the user.

The default path is C drive(i.e Sql server installed path) highlighted in above screenshot.

STEP 3 : 

The default path can be changed to store the master data file and Log data file.Refer the screenshot below.

Here I have changed my file path to E drive.(Highlighted in Red colour)

STEP 4 : 

Now You can see the data files created at the mentioned path in the step 3.

STEP 5 : 

Finally you can see the Sample database created in the below screenshot.

What is LDF and MDF in SQL Server?

LDF is abbreviated as LogDataFile and

MDF is abbreviated as MasterDataFile

MDF - In this file all database information are stored.All objects created in the

database are stored in master file.

LDF - In this file all transactions are stored.During Data creation and data modification all activity is logged in log database file.These file will grow for each data transactions.These file can be used for rollback purpose during emergency.

How to Create a Database in a Simply Manner ?

Syntax : 
Create Database [Database Name]

Eg : Create Database Sample

Now the database will be created with the name of Sample.
And the two files will be created automatically (i.e) ldf and mdf.

mdf - Master Data File
ldf - Log Data File

These two files will be located in SQL server installed location(i,e default location)
Unless you specified the path