How to Delete all the files from a folder expect the oldest file in SSIS Package - SSIS Tutorial

Scenario:

You are working as ETL Developer. You got this requirement where you need to delete all the files from a folder except the oldest. How would you do that?

Solution:

We can use Script task to handle this requirement. 

Step 1:

Create new SSIS Package and inside SSIS Package, create variable for folder path called FolderPath. I always like to create variables so we can use them in configuration and if we need to change the value in different environments such as QA,UAT and Production. It is very easy to do by using configuration instead of using multiple copies of SSIS Package.

How to delete all files in a folder by using Script Task in SSIS Package except oldest file-SSIS Tutorial




Step 2:
Bring Script task to Control Flow pane in SSIS Package and open by double clicking. Map the variable FolderPath to use in Script Task also choose the scripting language. I have selected C#.


SSIS - Delete all files from a folder except the oldest one - Script Task- SSIS Tutorial


Step 3:
Open Script Task and add System.IO namespace under NameSpace Region as shown below.


How to add System.IO namespace to NameSpace Region in Script Task to Delete Files


Step 4:
Add below code under Public Void Main() {

// TODO: Add your code here
            var directory = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());
            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MaxValue;
            string filename="";
      //Get the oldest file 
            foreach (FileInfo file in files)
            {
                 if (file.LastWriteTime < lastModified)
                {
                    lastModified = file.LastWriteTime;
                    filename = file.Name;
                    //MessageBox.Show(filename);
                }
            }
      //Delete all files except oldest one.
            foreach (FileInfo file in files)
            {      
                   //MessageBox.Show(filename);
                    if (file.Name !=filename)
                    {
                        file.Delete();
                    }
               
            }


Step 5:
Save your folder path in value of FolderPath variable. Run the SSIS Package. It should delete all the files in a folder except the oldest one. 




How to delete all files in a folder except Latest by using SSIS Package - SSIS Tutorial

Scenario:

We have a situation, where one of our process download files every day. The process runs on schedule and download multiple files.Our requirement is to load the latest file to SQL server Table. We don't care about old files, we want to delete them. How would you do that in SSIS Package?

Solution:


There are multiple ways to handle this situation, we can delete the old file and leave the latest and then can use the foreach loop to load the latest file to SQL Server Table.

In this post, I am going to do the first part. We will be using script task to delete all old files from a folder and leave the latest. To load file from a folder, you can take a look into Here.

Step 1:
Go ahead and create SSIS Package in your solution. Then create a variable called FolderPath as shown below.

How to delete all files in a folder except latest file-Create Variable for Folder Path- SSIS Tutorial




Step 2: 
Bring Script Task to Control Flow and Map the Variable FolderPath and choose the scripting language, I have selected C#.
How to delete all the files in a folder by using Scrpt Task in SSIS Package - SSIS Tutorial



Step 3:
Add System.IO namespace under Namespaces

Step 4:
Add the below code under public void Main() {

    // TODO: Add your code here
var directory = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());
            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MinValue;
            string filename="";
      //Get the lastest file name
            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime > lastModified)
                {
                    lastModified = file.LastWriteTime;
                    filename = file.Name;
                    //MessageBox.Show(filename);
                }
            }
      //Delete all old files except latest one.
            foreach (FileInfo file in files)
            {      
                   // MessageBox.Show(filename);
                    if (file.Name !=filename)
                    {
                        file.Delete();
                    }
               
            }


Save the script. You are all done.

Go ahead and provide the folder path in Value of FolderPath variable. Execute SSIS Package.It should delete all filed except latest.

Step 5:
Follow the steps  Here.to read the file and load to Table.

How to Alter column from Not Null to Null in SQL Server Table - SQL Server / T-SQL Tutorial Part 54

Scenario:

You are working as SQL Server developer for Auto Insurance Company, You were asked to create a table with below definition where Middle Name has to be provided always. So you have created the Column with Not Null property. After some time, your company encountered some data ,where customer had no Middle Name. They would like you to change the Column to accept Null if not provided. What scripts you will provide?


Create Table Customer
(FirstName VARCHAR(50) Not Null,
MiddleName VARCHAR(50) Not Null,
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Gender CHAR(1)
)

Solution:

You can simply Alter the column by using below statement.

Alter table dbo.TableName
Alter Column ColumnName DataType Size Null


To Alter Middle Name to accept Null, we can use below statement.

Alter table dbo.Customer
Alter Column MiddleName VARCHAR(50) Null

Get List of All Null and Not Null Columns in SQL Server Database - SQL Server / T-SQL Tutorial Part 53

Scenario:

You are working as SQL Server Developer, You are doing some analysis and want to get the list of all columns in SQL Server Database which are Nullable or Not Nullable.How would you get this information?

Solution:

We can use system views in SQL Server to get this information. Below Query will return us Database Name, Schema Name, Table Name, Column Name,Data Type and IS_Nullable.

SELECT Table_CataLog AS DBName
    ,table_Name
    ,Column_Name
    ,Data_Type
    ,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
  --Uncomment the Where Clause if you want to filter the records
  --where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'TableName' and COLUMN_NAME = 'ColumnName'


How to get list of Nullable and Non-Nullable Columns in SQL Server Database- TSQL Tutorial


How to Alter Column from Null to Not Null in SQL Server Table - SQL Server / T-SQL Tutorial Part 52

Scenario:

You are working as SQL Server developer with a Bank. They have Dbo.Customer table in TechBrothersIT database with below definition.

Create Table dbo.Customer
(FirstName VARCHAR(50) Not Null,
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Gender CHAR(1)
)

As you noticed that LastName can be Null. That is the problem. Company noticed that later and always want to have the value for LastName. You are asked to do analysis and write Alter script to Change Column from Null to Not Null. What things you will consider or suggestions you will provide?

Solution:

The very first thing to consider in this scenario is to find out if any values are already inserted in the table for Last Name as Null. If that is the case, you can't really go ahead and Alter column from Null to Not Null. If you try , you will get below error.

Msg 515, Level 16, State 2, Line 14
Cannot insert the value NULL into column 'LastName', table 'TechBrothersIT.dbo.Customer'; 
column does not allow nulls. UPDATE fails.

It means we have to deal with Null values in this column first. You can talk with business and ask them what they would like to do for all the customer where Last Name is Null. You can find the list of customers where Last Name is Null by using below query.

Select * From dbo.Customer
where LastName Is null

Business can provide different suggestions such as
Let's update the LastName to Unknow where it is Null
Or Update the Last Name to blank('') if it is Null
Or Update the Last Name column to LNNP (Last Name Not Provided)
Or they go back to customers and get their last Name to update 

Depending upon the suggestions, go ahead and update the values in Last Name column. Let's say we decided to update to blank '', we can use below query to update 

update dbo.Customer
set LastName=''
where LastName is Null


Now you are all set to change column in table from Null to Not Null.

Alter Table dbo.Customer
Alter Column LastName VARCHAR(50) Not Null



How to create Not Null Constraint on Column in SQL Server Table - SQL Server / T-SQL Tutorial Part 51

Scenario:

You are creating customer table for one of your requirement, you want to make sure the First name Column always have the value. How will you make sure First Name Column always have the value?


Solution:

Constraints defined rules which make sure the data we insert in column follow the rules.

For above requirement we can create Not Null constraint on a column, Not Null Constraint make sure the value inserted in that column is not Null. If user will try to insert row without providing the value for the column on which we have created the Not Null Constraint , The insert will fail as column can not accept Null values.

To create Not Null Constraint on Column in a Table, we have to Add Not Null after the Data Type as shown below for First Name Column.

Create Table Customer
(FirstName VARCHAR(50) Not Null,
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Gender CHAR(1)
)

You can see that I did not provide Not Null Constraint for other columns, that means those column can accept Null values or Unknown value.

Let's insert below rows and see what happen

insert into dbo.Customer ( FirstName,LastName,Age,PhoneNumber,DOB,Gender)
Values
('Aamir','Shahzad',Null,Null,Null,Null)
,(Null,'Raza',33,'00000000',Null,'M')

As you can see that the transaction failed as we can't insert the Null in First Name column, No Record will be inserted.


How to create Not Null Constraint on a column in SQL Server - TSQL Tutorial 

Let's consider second Insert , Where we are not inserting anything in First Name column. Still the insert will fail, as when you don't provide the column in insert list, it try to insert Null value for that column and we have created Not Null Constraint on First Name column.


insert into dbo.Customer (LastName,Age,PhoneNumber,DOB,Gender)
Values
('Raza',33,'00000000','2016-01-26','M')


How to create Not Null Constraint in SQL Server - SQL Server / TSQL Tutorial



What are different Types of Constraints Available in SQL Server - SQL Server / T-SQL Tutorial Part 50

What are Constraints in SQL Server?


In simple words, Constraints define rules those we implement to avoid insertion of any data that does not follow the rule.
By using the Constraint, we can validate the data and if complies the rule, It will be inserted in table otherwise rolled back.
Constraints are used to maintain Data Integrity. They make sure the data entered follow the rules we have defined.

There are different types of Constraints available in SQL Server.



  1. Not Null Constraint
  2. Check Constraint
  3. Default Constraint
  4. Unique Constraint
  5. Primary Key Constraint
  6. Foreign Key Constraint


How to Generate Add Column Statement for all the tables in a Database in SQL Server - SQL Server / T-SQL Tutorial Part 49

Scenario:

You are working in Auto Insurance Company as SQL Server Developer. Your company has a database in which they have more than 300 Tables. In initial design, they forgot to add Audit columns such as CreatedBy and CreatedOn. They want you to generate Add Column CreatedBy and CreatedOn for all the Tables in a Database, How would you do that?


Solution:

There are many ways to generate the SQL Statements for above requirement, we are going to keep it simple and quick. We will use Select Query to generate Add Column Statements for all the tables.

Let's generate the scripts in a way so we have to make very small changes every time and we can use anytime to add new column. I have declared two variables, @ColumnName and @ColumnDataType. Once we will provide the values for variables. The Select query is going to check all the tables and if column does not exist for the table, it will generate Add Column statement.


--Declare Variables for Column Name and Data Type
Declare @ColumnName VARCHAR(100)
Declare @ColumnDataType VARCHAR(50)

--Set the Values for Variables
SET @ColumnName='CreatedBy'
SET @ColumnDataType='VARCHAR(50)'

--Run the Query and copy results and paste in new window to run.
Select 'ALTER Table ['+Table_Schema+'].['+Table_Name+'] '
+'Add '+@ColumnName+' '+@ColumnDataType AS AddColumnQuery
 from Information_Schema.Tables T
where T.Table_Type='BASE TABLE'
And Not exists (
Select 1 from INFORMATION_SCHEMA.COLUMNS C 
WHERE C.TABLE_Name=T.Table_Name and Column_Name=@ColumnName)

Run above query and copy the results to new Query Windows, Select the Database on which you would like to run and execute.

How to Generate Add Column SQL Statement for all the tables in a database - T-SQL Tutorial



Video Demo : Generate Add/Drop  Column Statement for all the tables in a Database in SQL Server


How to Generate Drop Table Statement for all the tables in a database - SQL Server / T-SQL Tutorial Part 48

Scenario:

You are working as SQL Server developer in Credit Card Company. You need to generate Drop Table statements for all the tables in Database TechBrothersIT that you are using as Staging for Data Loading process. How would you do that?

Solution:

There are multiple ways to generate Drop Statements for All the tables in a database. We can use Object Explorer Detail Windows, Click HERE to see the blog and video. But in this post, We are going to use simple Select Query to generate Drop Statements for all the tables in a database.


SELECT 'Drop Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS DropTableQuery
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'


If you would like to filter the Tables by Schema, you can add Where Clause to Query or if you like to filter tables by name you can add Table_Name in Where clause.

Once you run above statement, Drop statements will be shown, copy them and run in new query.

How to Generate Drop Table Statements for all the tables in SQL Server Database - T-SQL Tutorial

Video Demo : How to generate drop table statement for all the tables in SQL Server Database

How to Get Identity Column Values without mentioning Identity Column Name in Select - SQL Server / T-SQL Tutorial Part 46

Scenario:

You took a quick look on Table and Columns in SQL Server but could not find if Identity Property is enabled on one of the column. You don't want to use system tables to find out the column name if identity is enabled or not but want to get the column name with data. How would you do that?


Solution:

You can use $Identity in your select, that will return all the values for identity Column.

Let's say we have table customer with couple of records.

CREATE TABLE Customer (
    CustomerId INT Identity(1, 1)
    ,FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )

insert into dbo.Customer(FirstName,LastName,Age)
    Values('Aamir','Shahzad',66),
    ('Raza','M',44)



To get Identity Column with Data, you can use below syntax

Select $identity from SchemaName.TableName

To get the identity column with values from our dbo.Customer table, we can use below script

Select $Identity from dbo.Customer

We will see below results.

How to get Identity Column Name and Values without using System tables in SQL Server


Video Demo : Get Identity Column values without mentioning Identity Column Name in Select

How to find all the tables with Identity Column in SQL Server Database - SQL Server / T-SQL Tutorial Part 45

Scenario:

How would you find all the tables in a SQL Server Database which has identity Column?

Solution:

We can use system tables such as sys.columns and sys.tables to get this information.

--Find out all the columns for all the tables on which Identity Property is enabled
SELECT DB_Name() AS DatabaseName
    ,OBJECT_NAME(c.OBJECT_ID) AS TableName
    ,c.NAME AS ColumnName
FROM YourDBName.sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE is_identity = 1






How to get Tables in a Database with Identity Column in SQL Server - SQL Server/T-SQL Tutorial



Video Demo: How to find all tables which has identity Column in SQL Server


How to add Computed Column in SQL Server Table - SQL Server / T-SQL Tutorial Part 47

Scenario:

You are working as SQL Server Developer for one of the Law Firm. You have created a dbo.Customer table by using below definition

CREATE TABLE Customer (
    CustomerId INT Identity(1, 1)
    ,FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )

You need to have Full Name that should consist of First Name and Last Name. You don't want to save the duplicate data into table by adding new column for Full Name. What are your options?

Also depending upon Age, We would like to Add New Column call IsSenior and mark it 1 if above 65 else 0.

Solution:

SQL Server provide us Computed Column, that is virtual column and it does not store data unless it is marked as Persisted. That means that we can create computed column (Full name) that will use First Name and Last Name data. By creating Computed Column we don't have to save duplicate data for full Name column.

Let's go ahead and create Computed Columns Full Name and IsSenior depending upon our criteria.

CREATE TABLE Customer (
    CustomerId INT Identity(1, 1)
    ,FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    ,FullName AS FirstName + ' ' + LastName
    ,IsSenior AS CASE 
        WHEN Age > 65
            THEN 1
        ELSE 0
        END
    )

We concatenated First Name and Last Name for Full Name and wrote the Case Statement for IsSenior Computer Column. Let's go ahead and insert couple of records.

  insert into dbo.Customer(FirstName,LastName,Age)
    Values('Aamir','Shahzad',66),
    ('Raza','M',44)

Noticed I have not inserted anything for FullName and IsSenior Columns. The values for these columns will be calculated when we will select the data.

How to add Computed Columns in SQL Server Table - T-SQL Tutorial


If you need to add Computed Column to exiting Table, you can use below syntax

Alter Table SchemaName.TableName
Add ColumnName AS Logic( such as FistName +''+LastName)

Let's say if we want to add FullName Computer Column to Customer table, we can use below script.

Alter table dbo.Customer
    Add FullName AS FirstName+' '+LastName


Drop Computed Column from SQL Server Table:
The syntax for dropping Computed or normal column is same.

Alter Table SchemaName.TableName
drop Column ColumnName

Let's say if we want to drop FullName computed column from dbo.Customer Table. We can use below script.

Alter table dbo.Customer
    drop column FullName

How to Add identity Column to Table by TSQL and GUI in SQL Server - SQL Server / T-SQL Tutorial Part 40

Scenario:

You are working as SQL Server Developer and you have created a dbo.Customer table by using below Script.

CREATE TABLE Customer (
    FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )



You need to add a column CustomerId that should start from 1 and every time new row inserts into table, it should increment by 1.


Solution:

SQL Server provide us the facility to add column like that. It is call Identity. You can provide the initial value(seed) and provide Increment value. 

Let's say if we would like to Add CustomerId to Customer Table with Starting Value=1 and Incremental value=1, Our script will be like below

CREATE TABLE Customer (
    CustomerId INT Identity(1, 1)
    ,FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )


Add Identity from Design Windows:
Let's say if you are creating table by using Design windows, you can add Identity from there as well as shown below.


Go to Database, Go to Tables and then use Right Mouse click and hit New. A window will open and you have to provide Column Name, Data Types and If Column allows Null or not. 

Once done, then click on CustomerId column that we want to make identity column. In Column Properties windows, Enable Identity and provide the Seed and Incremental value as shown below.

Once all done. hit save or Generate Change Script to run on any SQL Server Instances later.
How to Add Identity Column to Table in SQL Server - SQL Server / T-SQL Tutorial


Video Demo :  How to create table with Identity Column in SQL Server

How to Add or Drop Column by using GUI in SQL Server - SQL Server / T-SQL Tutorial Part 39

Scenario:

You have created table dbo.Customer by using below script in Database TechBrothersIT. 

CREATE TABLE [dbo].[Customer](
    [FName] [varchar](50) NULL,
    [LName] [varchar](30) NULL,
    [Age] [tinyint] NULL,
    [PhoneNumber] [char](9) NULL,
    [DOB] [date] NULL,
    [Gender] [char](1) NULL
) 

You received new requirement.You have to add new column "Address" to table and delete Column "Gender" from Table.

Solution:

In SSMS, you can use Design window to add columns and drop columns. You can also script the changes to run in other environments such as QA,UAT and Production.

Step 1:
Go to Database and then Tables tab and then find your required table. Right Click on Table and select Design.


How to Add Column or Drop Column in Design Windows in SSMS- SQL Server Tutorial



Step 2: 
Design windows will open, Go ahead at the end and add new column. Provide the Column Name and Data Type and if you would like to allow Null values for it or not.
Add Column to SQL Server Table in Design Mode in SSMS - TSQL Tutorial 



To Delete Column, Choose the Column and Then hit Right Button on your mouse and click Delete Column as shown below.
How to Delete Column in Design Windows from SQL Server Table - T-SQL Tutorial


Now you have two options, if you would like to save the changes to table, Go ahead and hit Save button. If you want to script the changes, Click on Generate Scripts Button so you can use in any environment.
Generate Add or Drop Column Scripts from Design Windows in SSMS - SQL Server Tutorial

Once you hit Generate Change Script Button, Below windows will appear and ask you to save the scripts as shown below. 

Add Column or Drop Column Scripts for SQL Server Table - T-SQL Tutorial 


Video Demo : How to generate Add Column or Drop Column Statement from GUI in SQL

How to Alter Columns or Generate Alter Scripts by using GUI in SQL Server - SQL Server / T-SQL Tutorial Part 38

Scenario:

You are working as SQL Server developer for Law Firm. You have created table customer in one of the Database "TechBrothersIT" by using below Script.

CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Age] [tinyint] NULL,
    [PhoneNumber] [char](9) NULL,
    [DOB] [date] NULL,
    [Sex] [char](1) NULL
)

You need to make below changes to dbo.Customer but don't want to write scripts by yourself. You want to use Graphical User Interface to generate these script or even make those change right away.

Change Sex Column Name to Gender
Change First Name to FName
Change LastName to LName and Max size to Varchar (30)


Solution:

Step 1:
Go to Database and then go to Tables tab. Once you see your required table, Right Click and hit Design.
How to generate Alter Script or Alter Table in Design Mode in SQL Server Management Studio


Step 2:
Once you hit Design, Design window will open. Start making changes as per your requirement.
How to Alter Table in SQL Server by using Design Windows - SQL Server Tutorial



Step 3:
Now you have two options, You can hit Save button and all changes will be saved. If you would like to generate scripts so you can use in DEV, QA , UAT and Prod. You can click on Generate Script Button and it will generate scripts for you.

How to generate Alter script for table from Design Windows in SQL Server - T-SQL Tutorial




Once you hit Generate Script Button, below window will open and you can save the scripts to file to use in any environment.
How to generate Modify Columns in SQL Server Table from Design Windows - T-SQL Tutorial



Video Demo : Generate Alter Column statements from Graphical user interface in SQL Server

How to create Table by using GUI In SQL Server - SQL Server / T-SQL Tutorial Part 37

Scenario:


You are working as SQL Server developer for Trading Company. They have some users who have permission to create tables in one of the Sandbox Database. You need to show them how to create table by using SSMS GUI. The users don't know much T-SQL , so they are interested only to create table by graphical interface.

Table Name: Customer
Column Names

First Name : String Data Type ( Max Size 50)
Last Name : String Data Type (Max Size 30)
Age : Integer
Phone Number: 9 Characters
DOB : Date
Gender: Single Character ( M or F)


Solution:

Step 1:
Connect to the SQL Server Instance where database exists. In our Case the Database Name is TechBrothersIT. Expand it and go to Tables tab. Right click and then hit New and click on Table as shown below.



How to create Table in SQL Server from Graphical user interface - SQL Server / T-SQL Tutorial 


Step 2:
A window will open, you will be able to provide Column Names and Data Types as you like. Also you can choose if Column can accept Null values or not. In our case, we are Ok to allow Nulls.

How to create Table in SQL Server by using Graphical User Interface - SQL Server / T-SQL Tutorial


Step 3:
Type all the columns and choose data types as per your requirements as shown. Once done. Hit Save button and it will ask you to provide the name you like for your table. I have provided Customer. 
How to create Table in SQL Server by using Graphical User Interface(GUI) - SQL Server Tutorial

Once you hit Ok, table will be created in Database.


Video Demo : How to use Graphical user interface to create table in SQL Server 

How to Rename a Column Name or Table Name in SQL Server - SQL Server / T-SQL Tutorial Part 36

Scenario:

You are working as SQL Server developer for Auto Insurance Company. On one of  SQL Server Instance, they have Database with name Insurance. In Insurance Database they have table dbo.Customer with below definition.

Create Table Customer
(FirstName VARCHAR(50),
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Sex CHAR(1)
)



You got the requirement to change the name of Customer table to USCustomers and also change the Sex column Name to Gender. You need to prepare SQL Scripts for QA, UAT and Production.


Solution:

Rename Table in SQL Server:

You can use system Stored Procedure to rename Table in SQL Server.

sp_rename 'OldTableName' , 'NewTableName'

Let's rename our Customer table to USCustomer by using sp_rename Stored Procedure.

EXEC sp_rename 'Customer','USCustomer'


Rename Column in SQL Server Table:
To Rename column in SQL Server Table, we can use the sp_rename system Stored Procedure as well. The only thing we have to add it to is 'Column' Parameter at the end.

sp_rename 'TableName.OldColumnName' , 'NewColumnName' ,  'Column'

Let's use sp_rename to change Column Name from Sex to Gender in dbo.Customer Table.

EXEC sp_rename 'Customer.Sex','Gender', 'Column'


Video Demo : How to rename SQL Server Table or Column in Table in SQL Server



How to Alter Table in SQL Server by using Alter Statement - SQL Server / T-SQL Tutorial Part 35

Scenario:

Let's say that you are working as SQL Server developer in Mortgage Company. In TechBrotherIT database, there is Dbo.Customer Table with below definition.

Create Table dbo.Customer
(FirstName VARCHAR(50),
LastName VARCHAR(50),
Age TinyInt,
PhoneNumber CHAR(9),
DOB Date,
Sex CHAR(1)
)


You got the requirement in which you have to generate Alter statement for below changes


  • Add Column Address to Customer Table
  • Modify column FirstName data type from VARCHAR(50) to VARCHAR(60)
  • Drop Column Age from Customer Table


Solution:

Add Column/Columns to Table:

To add Column to Table, we can use below Alter statement.

Alter Table TableName
Add Column_Name DataType (Size if required)

by using above script, Let's prepare our script to add Address Column to Customer Table.

Alter Table dbo.Customer
Add Address VARCHAR(50)


As address will be variable length character string, I selected VARCHAR data type and max length with 50 characters.

If you need to add more than one column to Table, you can add all of them in single Alter statement. In below statement I added two columns to customer table ColumnName1 and ColumnName2.


Alter Table dbo.Customer
Add ColumnName1 VARCHAR(50),
ColumnName2 Int



Modify Data Type of Existing Column in SQL Server Table:
You can use below Alter statement to modify data type of FirstName column in Customer Table.

Alter Table SchemaName.TableName
Alter Column ColumnName new DataType (Size if required)

Alter statement to modify FirstName data type from VARCHAR(50) to VARCHAR(60)

Alter Table dbo.Customer
Alter Column FirstName VARCHAR(60)

Drop Column in SQL Server Table:
To drop column in existing SQL Server Table, we can use below statement.

Alter table SchemaName.TableName
Drop Column Column_Name

To drop Age column from dbo.Customer table, we can use below statement.

Alter Table dbo.Customer
Drop Column Age

If you need to drop more than one column in Single Alter statement, you can do that as well.

Alter table SchemaName.TableName
Drop Column ColumnName1,ColumnName2,....


Video Demo : How to Alter Table in SQL Server Database by using Alter Statement




What is the difference between Float and Numeric/Decimal in SQL Server - SQL Server / T-SQL Tutorial Part 33

Float and Real are approximate data types.  Float and Real data types do not store exact values for many numbers.The value can be extremely closed.

Numeric/Decimal are fixed precision data types. It will stored the values with exact precision and scale what you have defined.

If you need to store data where small difference does not matter, you can use Float or Real. But if you need to have exact data such as for financial application. You should be using Numeric/Decimal or Money data type to save exact data, as small difference can impact in calculations.

Example:


Let's declare two variables and save same data and see what happen to output due to data type of them.

DECLARE @DecimalVariable DECIMAL(8, 2)

SET @DecimalVariable = 213429.95

DECLARE @FloatVariable FLOAT(24)

SET @FloatVariable = 213429.95

SELECT @DecimalVariable AS DecimalVariable
    ,@FloatVariable AS FloatVariable




Difference between Float and Decimal/Numeric in SQL Server - T SQL Tutorial 

As we can see in above snapshot, Decimal stored and showed exact values and where we have saved values in float, it rounded the values. 


As float/ real data types are approximate data types, Avoid using them using in Where clause specially with = or < > operators.

What is difference between VARCHAR and NVARCHAR in SQL server - SQL Server / T-SQL Tutorial Part 32

Varchar is variable length character data type which is used to store Non-Unicode data format. You can think of Non-Unicode as English characters.

NVarchar is also variable length character data type which is used to store UniCode data format. For Unicode data formats you can think of alphabets of Chinese, Japanese, Korean and Arabic language.

Varchar can stored 'English' characters and it take one byte to store each character. Nvarchar can store English and other languages characters. NVarchar take two bytes to store each character.

 Let's create two variables and check the space used by each variable.

Declare @Variable1 VARCHAR(20)
Declare @Variable2 NVARCHAR(20)

SET @Variable1='TechBrothersIT'
SET @Variable2='TechBrothersIT'

Select datalength(@Variable1) as VarLength1, datalength(@Variable2) as VarLength2

What is the difference between VARCHAR and NVARCHAR - SQL Server Tutorial


As we have declare @Variable2 as NVarchar, it took twice the space as compared to @Variable1 which is Varchar to store the characters.


Let's create a table with Varchar and NVarchar Data type columns and insert few records.

Create table dbo.VarcharVsNvarchar( VarcharName Varchar(50),NVarcharName NVARCHAR(50))
insert into dbo.VarcharVsNvarchar  Values
('عامر','عامر'),
('عامر',N'عامر'),
('TechBrothersIT',N'TechBrothersIT')

Select * from dbo.VarcharVsNvarchar
go
Select DataLength(VarcharName) AS CHARNameLength,DataLength(NVarcharName) AS VarNameLength 
From dbo.VarcharVsNvarchar

Varchar VS Nvarchar in SQL Server - TSQL Tutorial


Our first Select query returned us the data. Noticed that we have some garbage data ????. Even we have defined the data type of column NVarchar, it inserted the garbage. To insert Unicode data in Nvarchar type column, we need to Use N with data as you can see in second insert ( ('عامر',N'عامر')).

What is the Difference between CHAR and VARCHAR in SQL Server - SQL Server / T-SQL Tutorial Part 31

CHAR and VARCHAR both data types are used to store character strings.

Char is fixed width, That means that the storage size of char value is equal to the maximum size of the column/ Variable.

Varchar is variable length. The Storage size used by Varchar depends upon the actual characters saved.


Example 1:

Let's create couple of variables and then save the same string and check the storage used by datalength function in SQL Server.

Declare @Variable1 CHAR(20)
Declare @Variable2 VARCHAR(20)

SET @Variable1='TechBrothersIT'
SET @Variable2='TechBrothersIT'

Select datalength(@Variable1) as VarLength1, datalength(@Variable2) as VarLength2


What is the difference between CHAR and VARCHAR in SQL Server - T-SQL Tutorial

As you can see above, Char occupied 20 Bytes space for 20 characters even we saved only 14 characters. On other hand, Varchar used only 14 bytes to store 14 characters.


Examples 2:

Let's create a table with two columns, once char and other varchat data type. Store the same information and then check the space occupied by each value by using datalength function.

Create table dbo.CharVsVarChar( CHARName CHAR(50),VARName VARCHAR(50))
insert into dbo.CharVsVarChar  
Values
('Aamir','Aamir'),
('TechBrothersIT','TechBrothersIT')
,('Raza','Raza')
go
Select DataLength(CharName) AS CHARNameLength,DataLength(VarName) AS VarNameLength 
From dbo.CharVsVarChar


Char vs Varchar in SQL Server - SQL Server / T-SQL Tutorial 


We can see that Char always occupy the same space regardless number or characters saved. On other hand Varchar space occupied varies depending upon the number of characters saved.

In cases where we know that we are going to store fixed number of characters, we can use Char otherwise use Varchar.


Use Create Statement to Create Table in SQL Server - SQL Server / T- SQL Tutorial Part 34

Scenario:

An Excel file is provided to you with some information related to Customer with below columns
First Name,
Last Name,
Age,
Phone Number,
Data of Birth,
Sex

and you are asked to create Table in SQL Server Database and save the data in newly created Table.

Solution:

We can use create statement to create Table in SQL Server Database. Below is Create Statement


Create Table Table_Name
(
Column_OneName DataType(Size if required),
Column_TwoName DataType(Size if required),
Column_ThreeName DataType(Size if required),
Column_FourName DataType(Size if required)
)

If we would like to create table with our above requirement, our Create Statement with be

Create Table Customer
(FirstName VARCHAR(50),
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Sex CHAR(1)
)

There are few points to notice in above Create Statement. I have not used the Schema Name with Table Name. If you would not provide Schema Name with Table Name, it will be created with dbo schema.

Let's say if we have Sales Schema and we would like to create Customer in Sales Schema. Our Create Statement will be


Create Table Sales.Customer
(FirstName VARCHAR(50),
LastName VARCHAR(50),
Age TinyInt,
PhoneNumber CHAR(9),
DOB Date,
Sex CHAR(1)
)

VARCHAR(50) for First Name and Last Name : It will let us store characters and the max number will be 50.
TinyInt for Age: It will let us store integer value between 0 to 255.
CHAR(9) for PhoneNumber : Char will let us store characters, as we know that we need to store 9 Characters, we used Char(9).
Date for DOB: In Date of Birth column, we will be able to store date as we have data type= date.
Char(1) for Sex: As we would like to store M or F for Sex, Char is going to let us store single character.


Video Demo : How to use Create Statement to create SQL Server Table