Existence of Constraint

With the help of below code, we can find out that particular constraint exist or not on a table. Its always good practice to check the existence of constraint in constraint creation script.

SELECT *
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('<table name>')
AND name = '<constraint name>'

UNION ALL

 
This operator combines the result set of multiple queries or you can say it appends the result set of next query to the previous query result set.
Some points need to consider:-

• Number of columns in all result set or in all queries must be same.
• Order of columns must be same in all queries.
• Data type of columns must be same.
• It returns the unsorted result set.
• It returns all records whether its duplicate record or not.

 Below is the example:-

 

Index Created Date

With the help of below query, we can figure out the index created date:


SELECT si.name 'Index Name'
, so.crdate 'Index Created Date'
, OBJECT_NAME(so.id) 'Object Name'
FROM sysindexes si
INNER JOIN sysobjects so
ON so.id = si.id
ORDER BY crdate DESC

CASE Statement

We can use CASE statement in two formats:-

• Simple CASE statement, where CASE expression compares the given conditional value to determine the result.
• Search CASE statement, where CASE expression evaluates a set of Boolean expressions to determine the result

Some important points:-

• CASE statement supports optional ELSE argument.
• In the case of not supplying ELSE argument, NULL will be taken as default value for ELSE.
• CASE can be used in any statement like SELECT, UPDATE, DELETE, SET and in clauses like SELECT_LIST IN, WHERE, ORDER BY, HAVING.
• SQL Server allows for only 10 levels of nesting in CASE expressions.
• The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

Below is the example:-

 

 

 

Cross Join

By default, Cross Join returns Cartesian product OR we can say, Cross Join returns N*N result set.

Lets have an example as below:-

Now Cross Join both @tblA and @tblB :-

After apply filter criteria to Cross Join, we will get result set accordingly.

 

Full Outer Join

This logical operator returns all the records from any one of the table and non-matching records from the another tables which will be based on the joining condition.

Or, we can say

Non matching records from one table + matching records of both table + non matching records from another table

 

Example

 



Lets have data as below :-

 

 

 

Now use Full Outer Join (we can use as Full Join also) and have a look at returned result set as below :-

 

 

 

Right Outer Join

This logical operator returns all the records from right hand side table and similar records from left hand side table.

Example

Lets have data as below :-

Now use Right Outer Join (we can use as Right Join also) and have a look at returned result set as below :-

Facebook page


 

Left Outer Join

This logical operator returns all the records from left hand side table and similar records from right hand side table.

Example: Lets have data as below :-

Now use Left Outer Join (we can use as Left Join also) and have a look at returned result set as below :-

 

Outer Join

In the case of Outer Join, SQL Server returns all the data from at least one table and matching records from the another table based on the column being used in Join condition.

We have below type of Outer Join :-

• Left Outer Join
• Right Outer Join
• Full Outer Join

 

 

 

 

Inner Join

Inner join logical operator returns those records from more than one tables which have the exact value in the column which is being used in joining condition.
Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.

Example:-

If we have duplicate records then inner join behaves like cross join for those duplicate records.

Example:-

 

Join

Join operator allow us to join two or more than two tables to retrieve the desired result set.
Join operator relate the tables on the basis of logical relationship. For joining the tables, at least one column accross the tables must have the same meaning as per business.
We have following type of joins in SQL Server:-

• Inner Join
• Outer Join
• Cross Join
• Self Join

Between

In SQL Server, with the help of "Between" logical operator, we can provide the value range to test expression for evaluation.

Syntax:-

test_expression [ NOT ] BETWEEN <start value/expression> AND <end value/expression>

Note:- BETWEEN consider the range inclusive of start and end value for evaluation of test expression.

Example:-
Create a table and insert some records in table as below:-

Now let's make use of "Between" in our query:-

 

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 :-

 

Primary Key in SQL Server

Primary Key is a type of constraint which help us to maintain the integrity. Primary Key in brief is as below :-

• Primary key enforces the entity integrity.
• Primary Key can be created on a column or combination or column.
• Primary Key value/values uniquely identify each row in the table.
• A table can have only one Primary Key constraint.
• Primary key column can not accept null values as well as values must be unique.
• If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
• With Primary Key, by default Clustered Index get created on Primary Key column.
• When we create Primary Key constraint and we didn't specify the name of constraint then SQL Server provide the name of primary key constraint as per below format :-
  
   PK_<table name>_<16 digit alphanumeric character>
  
  for example :- PK__tbl__D8360F734316F928
  
• There are two way to create the Primary Key at the time of creation of table
  
• at column level
  
  for example:-
  CREATE TABLE tbl ( col INT CONSTRAINT pk_tbl_col PRIMARY KEY )
  
• at table level
  
  for example:-
  CREATE TABLE tbl ( col INT
                      , col2 INT
                      , CONSTRAINT pk_tbl_col PRIMARY KEY(col)
                   )
  
• Syntax to create the Primary Key on existing table
  
  ALTER TABLE <table name>
  ADD CONSTRAINT <constraint name> PRIMARY KEY(<primary key column>)
  GO
  
• Drop the Primary Key
  
  ALTER TABLE <table name>
  DROP CONSTRAINT <constraint name>;
  GO

COUNT() in SQL Server

Some considerable point about COUNT() in SQL Server :-

• It comes under aggregate function.
• It returns number of item in group.
• The return type of COUNT() is "INT".
• COUNT(*) returns the number of values in a group, doesn't matter values includes "NULL" or duplicates.
• COUNT(<ColumnName>) returns the number of nonnull values.
• COUNT(DISTINCT <ColumnName>) returns the number of unique, non-null values.

Lets have an example :-

% (Modulo) in SQL Server

Provides the remainder of one number divided by another.

Syntax:-

dividend % divisor


Example:-