DIFFERENCE BETWEEN CHAR AND VARCHAR IN SQL SERVER

CHAR: The CHAR datatype is a fixed length data type in sql server. It is used to store fixed length type of string data or character string.

The size of CHAR datatype is 1 byte of space for each character and it also store non-unicode character. 

CHAR supports upto 8000 characters.

Performance is good while we using CHAR datatype as compared to VARCHAR datatype.

When the length of  string characters is less than the specified length or fixed length then it occupies all bytes to store the string characters.

The use of CHAR in sql server is as below:

DECLARE @NAME CHAR(10)

SET @NAME = 'RAMAN'

SELECT @NAME AS NAME

SELECT DATALENGTH(@NAME) AS NAME_DATALENGTH, LEN(@NAME) AS NAME_LEN 

After execution of above scripts, the result would be:

NAME: RAMAN

DATALENGTH: 10

LEN: 5

You can see the below screenshots for further clarification:

VARCHAR: The VARCHAR is most widely used into the sql server to store non-unicode variable length type of data or string data. 

VARCHAR stores data at 1 byte per character and it can store non-unicode characters or string data. 

VARCHAR also supports upto 8000 characters.

VARCHAR is a variable length and it takes less memory space. 

Performance is not good while we using VARCHAR datatype.

When the length of  string characters is less than the specified length then it occupies only number of  bytes to store the string characters rather than all bytes.

The use of VARCHAR in sql server is as below:

DECLARE @NAME VARCHAR(10)

SET @NAME = 'RAMAN'

SELECT @NAME AS NAME

SELECT DATALENGTH(@NAME) AS NAME_DATALENGTH, LEN(@NAME) AS NAME_LEN 

After execution of above scripts, the result would be:

NAME: RAMAN

DATALENGTH: 5

LEN: 5

You can see the below screenshots for further clarification:

                 - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE COALESCE FUNCTION IN SQL SERVER

COALESCE: The  COALESCE function is used to coalesce may rows into a single row or one row. The coalesce function is also used to handle the NULL values. The basic example of Coalesce function is as below:

Here, we have an Employee table with list of 20 records in TESTDB on my local server. Please see the below screenshot.

So, here we have an EMP_NAME column in which the employee names are displaying in each row individually. So now we will coalesce all names into a single row with the help of Coalesce function in sql server. The changes are as below.

DECLARE @employeeNames VARCHAR(8000)

SELECT @employeeNames = COALESCE(@employeeNames + ' , ', '') + CAST(EMP_NAME AS VARCHAR) FROM   Employee

SELECT @employeeNames 

It will retruns all the employee names into a single row as like this:

RAMAN SACHDEVA , ANAND SACHDEVA , PALKA SACHDEVA , RITESH SINGH , ARIJEET SINGH , TARUN SHARMA , KULWINDER SINGH , KRISHAN KUMAR , RAJINDER KUMAR , ATUL SHARMA , FATEH SINGH , RAJDEEP SINGH , JAGJEET SINGH , ANKUSH KUMAR , ROSHAN SINGH , SAURABH KUMAR , VARINDER SINGH , NITISH KUMAR , YUVRAJ SINGH , HARDEEP KAUR

Please see an above example into the below screenshot for further clarifications.

          - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE DISTINCT WITH SELECT STATEMENT IN SQL SERVER

DISTINCT:- The DISTINCT keyword is used to fetch or get the unique records from the table with select statement. It eliminates or remove duplicates records and fetch only unique records from table. 

The syntax of DISTINCT keyword is used with Select Statement is as below:

SELECT DISTINCT  COLUMN1 FROM TABLE

Here, we have a table i.e. PRODUCTS with list of 15 records in TESTDB. Please see the below screenshot:

SELECT * PRODUCT_NAME FROM PRODUCTS

Now, we are using the DISTINCT keyword with Select statement to get or fetch the unique or different Product names from Products table with the below query.

SELECT DISTINCT PRODUCT_NAME FROM PRODUCTS

Here is an example of DISTINCT with Select Statement. Please see the attachment file as below:

   - - - - - - Cheers, Happy to Help! - - - - - - 

ALTER TABLE > ALTER COLUMNS > DROP COLUMNS IN SQL SERVER

 ALTER TABLE : The ALTER TABLE command is used to alter the columns in a table or add, deletes and modifies columns in a table. So the ALTER TABLE command is used as below:

We have an EmployeeDetail table in TESTDB database and we will add some new columns in EmployeeDetail table by alter command. The syntax od ALTER TABLE is as:

ALTER TABLE EMPLOYEEDETAIL

ADD CONTACT_NUMBER(18,2), ALTERNATE_CONTACT_NUMBER NUMERIC(18,2)

The example is as below:

Here, we have added two new columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. So if we want to change the data type of these columns then we will use ALTER COLUMN command.

ALTER COLUMN : The ALTER COLUMN command is used to change the data type of an existing columns in a table. So here we will change a data type of added columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. 

The syntax of ALTER COLUMN is as below:

ALTER TABLE EMPLOYEEDETAIL

ALTER COLUMN CONTACT_NUMBER(NVARCHAR(20)),

ALTERNATE_ CONTACT_NUMBER(NVARCHAR(20))

Note: The SQL Server doesn't allow to alter multiple columns with single ALTER TABLE command. 

The example is as below:

Here, we have changed data types of added columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. So if we want to drop these columns then we will use DROP COLUMN command.

DROP COLUMN : The DROP COLUMN command is used to delete or drop an existing columns from table. So here we will drop columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER from EMPLOYEEDETAIL table. 

The syntax of DROP COLUMN is as below:

ALTER TABLE EMPLOYEEDETAIL

DROP COLUMN CONTACT_NUMBER , ALTERNATE_CONTACT_NUMBER

The example is as below:

          

            - - - - - - Cheers, Happy to Help! - - - - - - 

Different types of Keys in SQL Server

Key: A key is a single or combination of more than one columns in a table. We can also say that a key is a field or columns in a table in database. There are the different types of keys in sql server. These are as below:

Primary Key: A primary key is a key that uniquely identify a each record in a table. A primary key can't accept null value while set primary key on a column during table creation in database. Here, ID column is a primary key of an Employee table. Please see the below screenshot.

Super Key: A super key is a set of attributes or set of columns which identify a specific row in a table. A Unique Key, Primary key and Alternate key are a subsets of Super Key. Here, ID, EMP_CODE, EMAIL columns are the super keys of an Employee table. Please see the below screenshot. 

Alternate Key: A alternate key is a field or set of fields that uniquely identify a record in a table. For example ID is a primary key and the other fields i.e. emp_code and email are the alternate keys in table. There will be only one column as primary key in a table. Here, EMP_CODE, EMAIL columns are the alternate keys of an Employee table. Please see the below screenshot. 

Candidate Key: A candidate key is a column or combinations of columns that identify a tuple in a table. There are more than one candidate keys in a table. A candidate key works as same as primary key that uniquely identify a tuples. Here, ID, EMP_CODE, EMAIL columns are the candidates keys of an Employee table.  Please see the below screenshot.

Unique Key: A unique key is a key that uniquely identify a record in a table.Its a combinations of one or more than one columns in a table. A unique key works as same as primary key but it doesn't allow duplicate value in a column. Here, EMP_CODE column is a unique key of an Employee table. Please see the below screenshot.

Foreign Key: A foreign key is a key that is used as a primary key of one table and its links as a foreign key in another table. Mainly, it creates relationship between two or more than two tables. There will be duplicate values in columns when using foreign key in the tables. Here, EMPLOYEE_ID column is a foreign key in EmployeeDetail table and its primary key i.e. ID column in Employee table. Please see the below screenshot. 

Composite Key: A composite key is a set of more than one columns of a table that uniquely identify a particular record in a table. 

           - - - - - - Cheers, Happy to Help! - - - - - - 

 

HOW TO RESTORE DATABASE BACKUP IN SQL SERVER

Step 1: First, we will create a new database with name as "NEWTESTDB". Please see the screenshot as below:

Step 2: Right click on the database name i.e. NEWTESTDB > Tasks > Restore > Database.

Step 3: You will see new window will be open and then select the following options. Please see the below  screenshot as below:

Step 4: Before restore a db backup, we have to do the following changes in Options menu (display at left side) and tick the Overwrite and Restrict checkbox. Please see the screenshot as below:

Step 5: After click on the OK button, the database has restored successfully. Please see the screenshot as below:

  

           - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE ORDER BY IN SQL SERVER

Order By: When we execute select statement to fetch the records or result sets from table in SQL Server then all records doesn't come in a sorting order. So at that time, we use Order By clause to sort the records or result sets in ascending and descending order.

The Order By clause is used into the SELECT Statement or SELECT Query.

The Order By clause is used after the WHERE Condition in SELECT Statement or SELECT Query.

Sometimes, the records or result sets fetch from table by default in ascending order. 

We can sort records or result sets in ascending and descending order by using Order By ASC|DESC.

Order By ASC: The Order By ASC clause is used to sort the records or result sets in ascending order. The syntax is as below:

SELECT * FROM TABLE WHERE CONDITION ORDER BY COLUMN

                           OR

SELECT * FROM TABLE WHERE CONDITION ORDER BY COLUMN ASC

Here, we have an EMPLOYEE table and we will apply sorting in ascending order into the select statement. Please see the below screenshot for further clarification.

Order By DESC: The Order By DESC clause is used to sort the records or result sets in descending order. The syntax is as below:

SELECT * FROM TABLE WHERE CONDITION ORDER BY COLUMN DESC

Here, we have an EMPLOYEE table and we will apply sorting in descending order into the select statement. Please see the below screenshot for further clarification.

                 - - - - - - Cheers, Happy to Help! - - - - - - 

 

DIFFERENCE BETWEEN MYSQL AND MS SQL SERVER

MYSQL: The MYSQL is an open source Relational Database Management System or RDBMS and it is developed by ORACLE now. There are some points mentioned that it differs that MS SQL SERVER. These are as follows:

MYSQL is an open source and it is freely available.

MYSQL  is more preferred and useful for PHP and open source based applications or projects.

MYSQL can run smoothly on several operating systems like Windows, Linux and Mac operating system.

MYSQL is an open source RDBMS and it saves data in tabular format.

MYSQL supports PHP, C++, JAVA, Python, Visual Basic Delphi etc. It also can additionally supports Perl, Scheme, Tcl, Haskel and Eiffel.

MYSQL blocks the database while backup data by extracting all data as sql statements.

MYSQL follows ANSI standards with some customization added with some functions and features.

MYSQL uses sql language to query the database.

MYSQL expects less amount of operational storage space.

When the query is running then it doesn't allow users to kill or cancel a query.

MS SQL SERVER: The MS SQL SERVER is also an open source Relational Database Management System or RDBMS and it is developed by Microsoft. There are some points mentioned that it differs that MYSQL. These are as follows:

MS SQL SERVER is an open source RDBMS  and it is not freely available.

MS SQL SERVER  is more preferred and useful for db products in Windows based environments.

MS SQL SERVER is mainly run on Windows.

MS SQL SERVER  is an open source RDBMS and it also saves data in tabular format.

MS SQL SERVER also supports PHP, C++, JAVA, Python, Visual Basic Delphi etc.

MS SQL SERVER doesn't blocks the database while backup data. It makes backup and restore huge faster.

MS SQL SERVER also follows ANSI standards with some customization added with some functions and features.

MS SQL SERVER is a query language and it creates and manipulate objects in RDBMS.

MS SQL SERVER expects large amount of operational storage space.

When the query is running then users have to kill the entire process to stop sql query execution.

            - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE LIKE OPERATOR IN SQL SERVER

 LIKE Operator: The LIKE operator is used to find a specified pattern to filter rows or records from a table.It is most widely used into the WHERE clause with SELECT, DELETE and UPDATE sql statements.

The two wildcards are often used with LIKE operator. These are as below:

a) % or Percent Sign

b) _ or Underscore Sign

a) % or Percent Sign: It represents 0, 1, or multiple characters.

b) _ or Underscore Sign: It represents a single character. 

The syntax of  LIKE operator is as follows:

SELECT * FROM TABLE WHERE COLUMN LIKE '%R%'

So, here we have a Employee table with list of records.

Now, we will use a LIKE operator to filter records from Employee table.

1. %SACH% : It fetches all the records from an employee table that have SACH character in any position. Please see the screenshot as belows:

2. R% : It fetches all the records that start with R character from an employee table. Please see the below screenshots.

3. %R : It fetches all the records that ends with R character from an employee table. Please see the below screenshots.

4. _R% : It fetches all the records from an employee table that have R character is in second position. Please see the below screenshots.

5. R_% : It fetches all the records from an employee table that have R character is in first position. Please see the below screenshots.

6. R%H : It fetches all the records from an employee table that have R character is in first position and H character is in last position. Please see the below screenshots.

                - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO CREATE DB BACKUP OF AN EXISTING DATABASE IN SQL SERVER

Step 1: Select an existing database and right click on it and select Tasks and then select Backup... option.

Step 2: After click on the Back Up... option, you will see next window i.e.

Here, you will remove destination backup file path by click on remove button or you can create backup file in above selected backup file path.

Step 3: After remove the selected file path, you can save backup file at your specified location. You will see into the below screenshot in which i will create backup file with named as "TESTDB.bak" in DB Backup direcory which is located in C Drive.

Step 4: Next, you have to click OK button.

Step 5: After click on OK button, you backup file will be created successfully. Please see the below screenshot:

                - - - - - - Cheers, Happy to Help! - - - - - - 

DIFFERENCE BETWEEN DELETE AND TRUNCATE IN SQL SERVER

DELETE: 

The DELETE command is used to delete the specific rows or delete all rows from a table with or without using WHERE clause. It contains WHERE clause if required.

The DELETE command doesn't reseeds Identity values.

It is a DML or Data Manipulation Language command.

We can Rollback the data or get the data back after using the DELETE command.

The DELETE command is slower than TRUNCATE command.

The example of DELETE command is as below:

        DELETE FROM EMPLOYEE WHERE ID = 1

                                   OR

        DELETE * FROM EMPLOYEE 

Here, we are deleting the record from Employee table whose ID = 1. You can see the below screenshot for further clarifications.

TRUNCATE: 

The TRUNCATE command is used to delete all the rows or records from a table without using WHERE clause. There is no any need of WHERE clause while use TRUNCATE command.

The TRUNCATE command reseeds Identity values.

It is a DDL or Data Definition Language command.

We can't Rollback the data or get the data back after using the TRUNCATE command.

The TRUNCATE command is faster than DELETE command.

The example of TRUNCATE command is as below:

       TRUNCATE TABLE EMPLOYEE 

Here, we are deleting all the records from Employee table by using TRUNCATE command. You can see the below screenshot for further clarifications.

                  - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO FETCH ODD/EVEN RECORDS IN SQL SERVER

Here, we will first insert a records into the Employee table and then we will fetch odd and even records from employee table. Please see the below screenshot i.e. adding multiple records in Employee table with insert query:

    FETCH ODD RECORDS: Now, we will first fetch the odd records from Employee table by using query i.e.

    SELECT * FROM EMPLOYEE WHERE ID % 2 != 0 

                                            OR

    SELECT * FROM EMPLOYEE WHERE ID % 2 < > 0

    Please see the below screenshot for further clarification.          

    FETCH EVEN RECORDS: To fetch the even records from Employee table, we will use query i.e.

    SELECT * FROM EMPLOYEE WHERE ID % 2 = 0 

    Please see the below screenshot for further clarification.       

                      

                   - - - - - - Cheers, Happy to Help! - - - - - - 

DIFFERENCE BETWEEN VARCHAR AND NVARCHAR IN SQL SERVER

 VARCHAR: The VARCHAR is most widely used into the sql server to store non-unicode variable length type of data or string data. 

VARCHAR stores data at 1 byte per character and it can store non-unicode characters or string data. 

VARCHAR supports upto 8000 characters.

VARCHAR is a variable length and it takes less memory space. 

The use of VARCHAR in sql server is as follows:

DECLARE @NAME VARCHAR(50)

SET @NAME = 'RAMAN SACHDEVA'

SELECT @NAME AS NAME

SELECT DATALENGTH(@NAME) AS NAME_DATALENGTH 

After execution of above scripts, the result would be:

NAME: RAMAN SACHDEVA

DATALENGTH: 14

NVARCHAR: The NVARCHAR is most widely used into the sql server to store unicode variable length type of data or string data. 

NVARCHAR stores data at 2 byte per character and it can store unicode characters or string data. 

NVARCHAR supports upto 4000 characters.

The use of NVARCHAR in sql server is as follows:

DECLARE @NAME1 NVARCHAR(50)

SET @NAME1 = 'PALKA SACHDEVA'

SELECT @NAME1 AS NAME

SELECT DATALENGTH(@NAME1) AS NAME1_DATALENGTH

After execution of above scripts, the result would be:

NAME: PALKA SACHDEVA

DATALENGTH: 28

You can see the below screenshots for further clarification:

                 - - - - - - Cheers, Happy to Help! - - - - - - 

DATEDIFF FUNCTION IN SQL SERVER

DATEDIFF: The DATEDIFF function returns the difference between two date values or returns the time between two dates in sql server.

Now, we will calculate Years,Month,Days,Hours,Minute,Second etc by using DATEDIFF function.

DECLARE @DATE1 DATETIME = GETDATE()

DECLARE @DATE2 DATETIME = '2020-08-15'

SELECT @DATE1 AS DATE1,  @DATE2 AS DATE2

CALCULATE YEAR:

SELECT DATEDIFF(YEAR,@DATE1,@DATE2) AS CALULATED_YEAR

CALCULATE MONTH:

SELECT DATEDIFF(MONTH,@DATE1,@DATE2) AS CALULATED_MONTH

CALCULATE DAY:

SELECT DATEDIFF(DAY,@DATE1,@DATE2) AS CALULATED_DAY

CALCULATE HOUR:

SELECT DATEDIFF(HOUR,@DATE1,@DATE2) AS CALULATED_HOUR 

CALCULATE MINUTE:

SELECT DATEDIFF(MINUTE,@DATE1,@DATE2) AS CALULATED_MINUTE

CALCULATE SECOND:

SELECT DATEDIFF(SECOND,@DATE1,@DATE2) AS CALULATED_SECOND

You can see the below screenshot for further clarifications,

              - - - - - - Cheers, Happy to Help! - - - - - - 

JOINS IN SQL SERVER

JOINS: The joins are used to get or retrieve records from more than one tables.It combines rows from two or more tables based on a common column between them. The four types of joins are used. These are:
a) LEFT JOIN
b) INNER JOIN
c) RIGHT JOIN
d) FULL JOIN

a) LEFT JOIN: The LEFT JOIN is also known as LEFT OUTER JOIN. When we use left join then It fetches all records from the left table and the matched records from the right table. And if there is no matching row in the right side table then the result shows NULL. The syntax of Left join is as:

     SELECT 
          COLUMN1(ID), COLUMN2 
     FROM 
          TABLE 1 
     LEFT JOIN 
          TABLE2 
     ON 
          TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

The TABLE1 is a left side table and TABLE2 is a right side table.

b) INNER JOIN: The INNER JOIN is also known as EQUI JOIN. When we use inner join then it fetches all records that have matched records in two or more tables. The syntax of inner join is as:

      SELECT 
          COLUMN1(ID), COLUMN2 
      FROM 
          TABLE 1 
      JOIN or INNER JOIN
          TABLE2 
      ON 
          TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

c) RIGHT JOIN: The RIGHT JOIN is also known as RIGHT OUTER JOIN. When we use right join then It fetches all records from the right table and the matched records from the left table. And if there is no matching row in the left side table then the result shows NULL.The right join or right outer join is similar to left join. The syntax of right join is as:

       SELECT 
            COLUMN1(ID), COLUMN2 
       FROM 
           TABLE 1 
       RIGHT JOIN 
           TABLE2 
      ON 
           TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

The TABLE1 is a left side table and TABLE2 is a right side table.

d) FULL JOIN: The FULL JOIN is also known as FULL OUTER JOIN. When we use full join then It fetches all records from the left table and right table.And if there is no matching row in the right side table then the result shows NULL The syntax of full join is as:

      SELECT 
          COLUMN1(ID), COLUMN2 
      FROM 
          TABLE 1 
      FULL JOIN or FULL OUTER JOIN
          TABLE2 
      ON 
          TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

The TABLE1 is a left side table and TABLE2 is a right side table.

            - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE SUB-QUERY IN SQL SERVER

A subquery is a query that are used nested inside into the another statements i.e select,insert,update,delete sql statements. A subquery is also known as inner query or nested query.

A subquery can be used in FROM Clause,WHERE Clause and SELECT Clause.

So, here we will use a subquery into the WHERE Clause.

First, we will create a product table and customer table and insert records into it. Please see the created Product table and Customer table.

Product Table:

Customer Table:

Now, we will use a subquery into the Where clause. The syntax is:

       SELECT 
ID,
PRODUCT_NAME,
PRODUCT_CODE,
PRICE 
FROM 
Product 
WHERE 
CUSTOMER_ID = (SELECT 
                                                 ID 
                                         FROM 
                                                 CUSTOMER 
                                         WHERE 
                                                 ID = 2)

Also, you can see the screenshot for further clarifications.

 
                     - - - - - - Cheers, Happy to Help! - - - - - - 

DATA TYPE RANGES IN SQL SERVER

Data Type   :   bigint
Range         :   -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
Storage       :   8 Bytes

Data Type   :   int
Range         :   -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
Storage       :   4 Bytes

Data Type   :   smallint
Range         :    -2^15 (-32,768) to 2^15-1 (32,767)
Storage       :    2 Bytes

Data Type   :   tinyint
Range         :   0 to 255
Storage       :   1 Byte

Data Type   :   bit
Range         :   0 to 1
Storage       :   1 Byte

Data Type   :   decimal
Range         :   -10^38+1 to 10^381-1
Storage      :   5 to 17 Bytes

Data Type   :   numeric
Range         :   -10^38+1 to 10^381-1
Storage       :   5 to 17 Bytes

Data Type   :   money
Range         :   -922,337,203,685,477.5508 to 922,337,203,685,477.5807
Storage       :   8 Bytes

Data Type   :   smallmoney
Range         :   -214,478.3648 to 214,478.3647
Storage       :   4 Bytes

Data Type   :   real
Range         :   -3.40E+38 to 3.40E+38
Storage       :   4 Bytes

Data Type   :   float
Range         :   1.79E+308 to 1.79E+308
Storage       :   It depends on the value(n)

Data Type   :   char
Range         :   0 chars to 8000 chars
Storage       :   n bytes

Data Type   :   varchar
Range         :   0 chars to 8000 chars
Storage       :   n bytes + 2 bytes

Data Type   :   varchar(max)
Range         :   0 chars to 2^31 chars
Storage       :   n bytes + 2 bytes

Data Type   :   text
Range         :   0 chars to 2,147,483,647 chars
Storage       :   n bytes + 4 bytes

Data Type   :   nchar
Range         :   0 chars to 4000 chars
Storage       :   2 times n bytes

Data Type   :   nvarchar
Range         :   0 chars to 4000 chars
Storage       :   2 times n bytes + 2 bytes

Data Type   :   ntext
Range         :   0 chars to 1,073,741,823 chars
Storage       :   2 times the string length

Data Type   :   datetime
Range         :   1753-01-01 to 9999-12-31
Storage       :   8 bytes

Data Type   :   smalldatetime
Range         :   1900-01-01 to 2079-06-06
Storage       :   4 Bytes, Fixed

Data Type   :   date
Range         :   0001-01-01 to 9999-12-31
Storage       :   3 Bytes, Fixed

Data Type   :   time
Range         :   00:00:00.0000000 to 23:59:59.9999999
Storage       :   5 Bytes

Data Type   :   datetimeoffset
Range         :   0001-01-01 to 9999-12-31
Storage       :   10 Bytes

Data Type   :   datetime2
Range         :   0001-01-01 to 9999-12-31
Storage       :   6 Bytes

Data Type   :   binary
Range         :   0 Bytes to 8000 Bytes
Storage       :   n Bytes

Data Type   :   varbinary
Range         :   0 Bytes to 8000 Bytes
Storage       :   The actual length of string + 2 Bytes

Data Type   :   image
Range         :   0 Bytes to 2,147,483,647 Bytes

                         - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO CREATE A FUNCTION AND USE IT INTO THE STORED PROCEDURE IN SQL SERVER

The function is a sql statements that returns a value. So here,we will create one scalar function and use it into the Stored Procedure in sql server. The process is as follows:

Step 1: Open the database that you created in your system.

Step 2: Click on the Programmability > Functions then you will see the Scalar-valued Functions option and right click on it then you will see an option i.e. New Scalar-valued Function. Click on it. Please see the below screenshot.

After click on the New Scalar-valued Function, you will see new window on where you can write your functions or sql statements.

Here, we will create a function to get the employee salary by passing employee id into the function. Please see the below screenshots.

Now, we will use the above function into the stored procedure.

So, here we have successfully created a new stored procedure and used above function into it by pass employee id. So, now we will see that how we will execute our stored procedure and get salary from the above created function. Please see the below screenshot:

                       - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO CREATE A STORED PROCEDURE IN SQL SERVER

Stored Procedure is a sql statements that you can write your own logic with sql commands or modify an existing stored procedures. It is a pre-compiled format and it also improves the performance while accessing data from database. Stored procedures are cached and can be reused. So here we will see that how we can create a new stored procedure in sql server. The process is as follows:

Step 1: Open the database that you created in your system.

Step 2: Click on the Programmability menu then you will see the Stored Procedure option and right click on it then you will see an option i.e. Stored Procedure. Please see the below screenshot.

After click on stored procedure, you will go to a new query window on where you can write sql statements or logic. Please see the below screenshot.

So, now you will create a procedure to add a new employee into an Employee table. Please see it below.

When you will be done with your changes in procedure then you have to execute a complete query at once by pressing F5. After that, your procedure would be create successfully.

                                  - - - - - - Cheers, Happy to Help! - - - - - -