Change column data types

Below are the syntax to change the data type of a column in SQL Server table :-

ALTER TABLE <schema>.<table_name>
ALTER COLUMN <column> <new data type>

Example:-

Let's create a table and confirm it's schema:-

 

Now we change the data type of column "name" from "CHAR(100)" to "VARCHAR(10)" and confirm the schema of table:-

* If we have data in our column then other data related conditions should also get considered before changing the data type of a column.

Change the Column Name

With the help of "sp_RENAME" system stored procedure, we can change the name of column in table.
Below is the syntax:-

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

Let's have an example:-

Create a table and have a look into the schema of table:-

Now with the help of sp_RENAME system stored procedure, we rename the column name from "id" to "emp_id" as below:-

 

Check if table exist

Below are some methods to check the existence of table in SQL Server database.

Method 1 :-

IF OBJECT_ID ('<database_name>.<schema_name>.<table_name>') IS NOT NULL

BEGIN

       PRINT 'Table exist'

END

ELSE

BEGIN

       PRINT 'Table does not exist'

END

Method 2 :-

SELECT *

FROM sys.objects
WHERE name = '<table name>'
AND type = 'U'       -- for user defined tables

 

Referential Integrity

In the case of Relational Database Management System, we have to maintain the relation across the table. Tables are related to each other on the basis of logical relationship. We maintain the relationship across the table to avoid having corrupt, irrelevant data to maintain the data integrity in our system.

Here Referential Integrity comes into the picture.

In the case of referential integrity, one table behaves like a parent table and another one behaves like a child table. Referential integrity enforces to have the defined relationships between tables when rows are entered or deleted. Referential Integrity maintains the key value, consistent across the table. The consistency will be in such manner that the child table should not have any value which is not referring to key value of parent table. If the key value gets changed in Parent table then the references to the key value should change accordingly.

Entity Integrity

An entity could be a person, place, thing or any real world object.

In SQL Server, each record of a table have an info regarding an entity. Let's take a real time example, we have employee as an entity which can be depicted as a "employee" table in SQL Server where we have column emp_id, emp_name, emp_address,..... etc. So here emp_id help us to identify the each record in table "employee" uniquely.

Here word "Unique" is most significant. Each employee has uniqueness or individual significance across the world. In employee table we can have more than one employee with same name but each employee should identify uniquely and must get unique employee id so that each employee can be identified individually or UNIQUELY.

To identify each record in a table uniquely, here ENTITY INTEGRITY concept comes into the picture.

The concept of Entity Integrity help us to define a row as a unique entity for a particular table. Entity integrity enforces the integrity of the key column, identifier columns or the primary key of a table, with the help of UNIQUE indexes, UNIQUE constraints or PRIMARY KEY constraints.

Domain integrity

Domain integrity help us to determine the column data type in a table as well as define the set of all allowed values in a column.
Or we can say, A domain integrity includes not only the concept of enforcing data types, but also the values allowed in the column.


Facebook page

SQL SERVER : Difference between Decimal and Numeric data type

• "decimal" and "numeric" data types both are exactly same or we can say They are synonymous to each other and either one can be used.
• "decimal" and "numeric" both data types are numeric data types with fixed precision and scale.
• "numeric" data type is functionally equivalent to "decimal" data type.

Syntax:-
decimal[(p[, s])]
numeric[(p[, s])]


facebook

Logical Operator Precedence in SQL Server

• Just like operator precedence in Mathematics, in SQL Server for LOGICAL OPERATOR, we have the precedence.
• When we have multiple operators in our expression, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.
• With the help of parentheses, we can control the operator precedence. Everything within the parentheses is evaluated first to yield a single value before that value can be used by any operator outside the parentheses.
• If an expression has nested parentheses, the most deeply nested expression is evaluated first.
• When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression.
• When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic, and bitwise, operators are handled before logical operators.

Logical operator precedence more specifically per the below table:-
 

EXECUTION Level	Operators
1	~ (Bitwise NOT)
2	* (Multiply), / (Division), % (Modulo)
3	+ (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
4	=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5	NOT
6	AND
7	ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
8	= (Assignment)

 

LIKE operator in SQL Server

It helps us to get the records for which column value character strings matches the specified pattern.
A pattern can have,

• Regular characters
• Regular characters with Wildcard characters

In the case of, during pattern matching, regular characters must exactly match the characters specified in the character string. Wildcard characters facilitate us to get the character string which are closely similar to mentioned pattern or can be matched with arbitrary fragments of the character string.
 
Syntax
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
 
Arguments

• match_expression:- Is any valid SQL Server expression of character string data type.
• Pattern: - Is the pattern to search for in match_expression, and can include these valid SQL Server wildcard characters.

Wildcard character	Description
%	Any string of zero or more characters.
_ (underscore)	Any single character.
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

• escape_character:- Is any valid SQL Server expression of any of the data types of the character string data type category. It has no default and must consist of only one character. It helps us to deal with data having special character.

Example is as below:-

Let’s have data as below:-

Now if we are using regular characters only with LIKE then result will be as below:-

Now if we are using regular characters with wildcard character “%” then:-

Now if we are using regular characters with wildcard character “_” then:-

Now if we are using regular characters with wildcard character “[]” then:-

Now if we are using regular characters with wildcard character “^” then:-

Now come to the ESCAPE clause which facilitates us to handle special character in our data. Now let’s assume, if we want all the records which have data exact to “A%B” then how will we handle?
To get the better understanding first have the look at below query and the result returned by that:-

Now to handle the special character so that we can get the result set having the exact value (“A%B”), have a look at below query and its result:-

 

WITH ROLLUP in SQL Server

• ROLLUP operator helps us to get the sub-totals and totals.
• Main benefit of ROLLUP is, it generates a result set that shows aggregates for a hierarchy of values in the selected columns.
• ROLLUP operator starts working from granule level. In other words, ROLLUP operator first applies the aggregate function (used in query) to individual record then on group of records then on total records.

For example:-

Below will be the result :-