SQL Server Tutorial available here covers detailed explanation from basic concepts to high level concepts which are explained in easy manner with simple English and good examples. This material is prepared by top lectures and professors who have 20 years experience in software industry.
SQL Server Tutorial is useful for B.tech CSE , IT , MCA, BCA and other computer science and information technology related students. This Tutorial will help to prepare for entrance exams, college exams and even interview exams for software companies.
SQL Server subject is one of the important subject for every student in order to have good future in software Industry. Our basic idea is to help students to learn this subject for free of cost by providing quality material which are prepared by top lecturers.
SQL Tutorial is divided by different lessons where each lesson covers several topics.
SQL Server Introduction
SQL SERVER Tutorial – Lesson 1 Different Products from Microsoft: Operating System: Examples are Windows XP, 2K3, Win7, Win8 Database Servers: Examples are SQL Server, MS Access Application Development Tools: Examples are C#.Net, VB.Net and ASP.Net Enterprise Resource Planning: Examples are Microsoft Dynamics Data Warehousing: Examples are Microsoft Business intelligence (MSBI) SQL SERVER Introduction: It […]
Database Introduction
DATABASE: Database is a “collection of interrelated data”. Ex: Sales D.B contains Sales data for ex: consumer’s products etc. DATABASE TYPES: 1. OLTP: It is simply called Database 2. OLAP: Data warehousing. D.B: -“Organizations Creates Database is to keep details about day to day transactions”. -The Basic Operators Performed on Database are […]
Components of RDBMS
Languages: Sql server: TSQL Oracle: SQL, PL/SQL TOOLS: SQL server: SSMS, SSBIDS Oracle : SQL*PLUS, TOAD Different Types of Keys in RDBMS:- [components of RDBMS] The different keys are Primary key Composite key Candidate key Super key Foreign key. PRIMARY KEY in SQL Server with example :- What is Primary Key in SQL Server? A column that uniquely identifies each record […]
Database Design and Development in SQL Server
DATABASE DESIGN in SQL Server:- Steps followed in database development: Analysis Design Development Implementation Maintenance. Db designed by “DB Designer(or) DB Architect” The DB designer designed the db by using two techniques, (i) ER model: Top-down Approach […]
Normalization
SQLServer Normalization:– Normalization Definition: Normalization process of organizing the data in any table and normalization in the process of reducing redundancy in the table. Normalization process determines which attributes should be grouped in a table. If the table contains redundancy the following problem in this problem are called “anomolies”. Insert anamoly Update anamoly Delete anamoly […]
Operations Performed over SQL Server Database
Sql Server Language :- TSQL SQL Server Tools :- SSMS SSBIDS SQL Server Profiles DB Tuning Advisor. Transact-SQL (T-SQL): User a TSQL a SQL Server DataBase TSQL is a language used to communicate with SQL Server. TSQL is based on SQL language. SQL is introduced by IBM and internal name of that language
[SQL]is called “SEQUEL” and later […]
SQL Server Versions and Instances
Sql Server Versions :– Sql server 6.5 Sql server 7.0 Sql server 8.0
Sql server 9.0
Sql server 10.0
Sql Server Instances :– Each installation of sql server […]
Services and Tools required while installing SQL Server
When we install sql server, we will be getting the following services :- DATA BASE ENGINE Integration Services Reporting Services Analysis Services Among those services integration service is common to all sql server instances. We configure each instance separately. The first installation is called “default-instance “and next installation is called “Named Instance” When you install […]
How to Create a new Database
CREATING A NEW DATABASE: To create a new db in an object explorer select databases -> right click -> SSMS ->Select ->new data base ->Object explorer side ->Server(SQL server 9.0.1399-sa) ->it is a instance ->Database ->Right click and Select ->New db In New DB: Enter DB Name : DB6PM At the time of creating DB sql server mainly creates two files, one file is […]
Data Types in SQL Server
DATA TYPES IN SQL SERVER:- 1) String type: – In sql server, string comparison is not case sensitive Small or alphabet, lower or upper case (a) Char : It allows char Data up to 4,000 bytes Example:- ename char (20) It is called fixed length character data types. Example: ravi 20bytes […]
SQL Server Operators List
Sql Server Operators :- Arithmetic Operators: +,-,*,/,% Relational Operators: >, <,>=, =, <> (or)! = Logical Operators: And, or, not Special operators: Between, in , like, is, any, all, exists, pivot [from 2008] Set operators: Union, union all, intersect, except. Creating a table:– Syntax: Create table <tablename> (Column name data type[(size)] Colname […]
Retrieving Data from Table
Retrieving Data from Table :- “Select “command is used to retrieve data from table. “Select” command is used to the following operations. (i) Select (ii) Project (iii) Join (iv) Set operations -> union, intersect These operations are data retrieval operations. SELECT :- Selecting particular records […]
Alias in SQL
ALIAS:- Alias means another name (or) alternative name. It is two types. (i) Column Alias -> Alias declared for column is called column alias (ii) Table alias -> Alias declared for table is called Table alias Column Alias: Syntax: Colname/ Example : Select ename, sal, sal*12 as Anusal from emp. Ename […]
Clauses in SQL Server With Examples
Clauses in SQL SERVER:- Different clauses in sql server Where Order by Distinct Group by Having With On where clauses in sql server :- Where clauses is used to get particular records from the table based on a condition Syntax: Select<collist>from<Tabname> where <condition>. Condition in Where clause query: Colname operatior value: It must be relational (or) […]
Between Operator in SQL Server
Between operation:- Between operation is used to compare column Value with range of values [it includes lower & upper values] Syntax:-Between v1 and v2 (or) Not between v1 and v2. Example:-Select * from emp Where sal between 2000 and 5000 Note: – Between operator always works with lower limit & Upper limit but not upper limit & […]
IN Operatior in SQL
In operation :- It is used to compare column values with multiple values. Syntax: – in (v1, v2, v3—) Not in (v1, v2, v3—) Example:- Select * from emp where job in (‘clerk’,’manager’) Display employee list who are not working for 10 (or) 20th departments. […]
Like Escape and IS operator in SQL
Like Operator in Sql Server Example:- It is used to compare column value with character patterns. Syntax: Like’pattern’ Not like ‘pattern’. The pattern consists of a-z , 0-9, Metacharacters -> % (this is to replace none (or) meta characters). _ [Under score] -> to replace […]
Order by Clause in SQL Server
Order by clause :- It is used to sort data return by the select statement based on one (or) more columns. Order by clause sort data in descending order (or) In ascending order. Syntax : Select <collist>from<tabname> [ where<cond>] Order by <collist>[asc\desc] [Note […]
Distinct Clause in SQL Server
DISTINCT CLAUSE: The main purpose of using this distinct clause is to eliminates duplicates. Syntax of Distinct Clause : distinct <column>/<collist> Example: Select distinct job from emp Job Analyst Clerk Manager Salesman Example:- Select distinct deptno, job from emp Deptno […]
Case Statement in SQL Server 2008
CASE STATEMENT :- Case statement works like if then else. It is a ANSII standard. Case statements are two types : Simple case Searched case 1) sample case statement in sql server :– Syntax : Case expression When value1 then return expression1 When value2 then return expression2 […]
DML Commands in SQL Server 2008
DML COMMANDS :- Insert Update Delete Merge Insert Command in SQL Example: Copying the data from one table to another table:- (i) Syntax: insert into <target table> Select * from <source table> Note: Both target table & source table structure must be same. Example: insert into emp1 Select * from emp {coping all records […]
Differences between Delete command and Truncate Command
Differences between Delete command and Truncate Command: DELETE Command (i) Delete command deletes all or particular Records (ii) Delete will not release memory. (iii) In delete, restoring is possible. (iv) Delete will not reset identity TRUNCATE Command (i) Truncate deletes only all records. (ii) Truncate releases memory. (iii) Truncate restoring is not possible. (iv) Truncate […]
DDL Commands in SQL with Examples
DDL COMMANDS :- Create Alter Drop Truncate. Create Command in SQL Server Creating a table from another table :- Syntax :- Select <collist> into <New tabname> From <old tabname> [where <cond>] Example:– Select * into <new emp10 […]
Identity in SQL Server
IDENTITY:- It is used to generate serial numbers for a column in a table mostly for a primary keys. Syntax :- identity (seed,incr) Seed ->Starting value. Incr -> increment by value Both seed,incr […]
Integrity Constraints in SQL Server
INTEGRITY CONSTRAINTS :- Business rules can be implemented in two ways : Declarative Approach Procedural Approach Declarative using integrity constraints Procedural using database triggers. If business rules are simple,then they com be implemented by using Integrity constraints. If business rules are complex, then the database-triggers. Integrity constraints used to : implement the business rule and Used […]
Different Integrity Constraints in SQL Server
Different Integrity Constraints in SQL Server UNIQUE NOT NULL PRIMERY KEY CHECK FOREIGN KEY Integrity Constraints: Entity Integrity (Unique, Primary Key) Domain Integrity (Not Null, Check) Referential Integrity (Foreign Key) (i) unique key integrity constraints:- Unique does not allow duplicate values. […]
Self Referential Integrity Constraint in SQL
SELF REFERNTIAL INTEGRITY :- A foreign key in one table refers primery key of same table then it is called “self referential integrity” and also called “Unary relationship” Empno ename manager[…]
Default Constraint in SQL
Default Constraint :– This is to provide default value for a column. When user doesn’t provide any value then sql server inserts default value. Declaration : Create table emp88 (eno int, Ename varchar(20), Doj date time default getdate()) Insert into emp88 values (1,’a’,16-oct-09) Insert into emp88 values (2,’B’,17-oct-09) Default <– Insert into emp88 (eno,ename) values (3,’B’)
Adding Constraint in a Existing Table
Adding constraint in a existing table :- Alter is used to add constraint in an existing table. Example :- Create table emp88 (eno int, Ename varchar(20), Sal smallmoney, Dno int) Adding primary key :- Syntax : {for any constraint} Alter table <tabname> Add{constraint<name>}type(collist) Note:- Before adding primary key constraint make The column is not null column then add primary key. Sp_help […]
Dropping Constraints in SQL
DROPPING CONSTRAINTS :– Syntax :– Alter table<tabname> Drop constraint<name> Alter table emp88 drop constraint ck_sal_emp88 -> Check constraint will be dropped. The primary key cannot be dropped,if it is referenced by any foreign key. DELETE RULES :– “if specifies how child record is effected if the parent record is deleted”. on delete no action on delete cascade on […]
Update Rules in SQL
UPDATE RULES :- The different update rules are On update no action On update cascade On update set null On update set default The update rules specifies how child record is effected if primary key of the parent record is updated. (i) on update no action :- Parent can’t be updated it matching child record exists. (ii) […]
Disable and Enabling the Constraints in SQL
DISABLE AND ENABLING THE CONSTRAINTS :- If the constraint is disabled, the constraint is exists in the table, but it will not work. Syntax :- no check Example :- alter table emp22 No check […]
Joins (inner join, outer join, cross join) in SQL
JOINS :– The join is a data retrieval operation performed to get data multiple tables. DB DWH tables […]
ANSI Joins OR SQL/92 Joins
ANSI JOINS OR SQL/92 JOINS :- Example :- Select e.ename, d.dname from emp e inner join dept d on e.deptno=d.deptno Joining 3 tables :- Example :- Select e.ename , d.dname, x.expr from emp e inner join dept d on e.deptno=d.deptno Inner join Emp_expr x On x.empno=e.empno.
Non Equi Join in SQL Server
NON- EQUI JOIN :- Joining two tables not based on common column it’s called Non-equi join. Syntax :- Select <collist> from <tablelist> Where <join cond> [and<join cond> and<cond>] Here join condition is based on other than equal operator,so it is Called non-equi join Emp […]
Self Join in SQL Server
SELF JOIN :- A self join is a joining table to itself. To perform self join the same table must be listed twice in different Alias. Self join is performed in table having self referential integrity. Display employee names and manager names: Select x.ename, y.ename manager From emp x,emp y Where x.mgr= y.empno Display names […]
Outer Join in SQL Server
OUTER JOIN :- Equi joins returns only matching records but not un-matching records. To get the un-matching record also,we need to perform,”out-join”. Outer joins are three types Left outer join Right outer join Full outer join (I) Left outer join:- Left outer joins returns all records from left side table and matching records from right side […]
Set Operators in SQL Server
SET OPERATORS :- The different Set Operator are (I) UNION (II) UNION ALL (III) INTERSECT (IV) EXCEPT Syntax :- Select statement1 Union/union all/intersect/except Select statement 2 (I) UNION Operator […]
Functions in SQL Server
FUNCTIONS :- Built in functions in sql server categorized into two categories: Single row Functions Multi row Functions (I) Single Row Functions in SQL: These function are capable of processing are row at a time and returns one value per row. Single row function are catagorized into following categories : STRING DATE MATHEMATICAL CONVERSION SPECIAL OLAP
String Functions in SQL Server
STRING FUNCTIONS : (A) UPPER FUNCTION : This function converts a string into upper case. Syntax :- UPPER(STRING) Example :- Select upper(‘hello’) Select empno,upper(ename) from emp. (B) LOWER FUNCTION :- It is converts a string into lower case. Syntax :- Lower(string) Example :-Select lower (‘hello’) (C) LEN FUNCTION :- It returns string length. Syntax :- len (string) […]
Date Functions in SQL with Examples
Date Functions in SQL :- Getdate () Function In Sql Server :- This function returns correct date Example :-select getdate ( ) Year ( ) Function In Sql Server :- Returns year part of the date Syntax :-year (date) Example :- Select year(getdate()) Display employee records joined in last year. Select * from emp where year (hiredate)%4=0 Month ( […]
MATHEMATICAL FUNCTIONS :- Abs( ) Mathematical Function in SQL:- It returns absolute value. Example :- Select abs (-10) ↓ Output →10 Sign ( ) Mathematical Function in SQL:- Syntax :- Sign(expr) If expr is + (plus) → 1 – (minus) → […]
Conversion Functions in SQL Server
CONVERSION FUNCTIONS :- This functions is used to one type to another type. Cast ( ) Function in SQL Server with Examples:- Syntax :- cast (expr as type) Example :- Select ename+earns+ cast(sal as varchar) from emp Output :- Smith earns 2000 Anu earns 4000 Example :- Select ename+joined on +cast(hiredate as varchar) from emp. Select ename+joined […]
Special Functions in SQL Server 2005
SPECIAL FUNCTIONS :- db_name ( ) SQL Function: Returns current name of the current database. Select db_name ( ) Output → mydb user_name( ) SQL Function: Returns name of the user Select user_name ( ) Output → dbo {database owner} host_name ( ) SQL Function: Returns name of the server Select host_name() Output → IEEE ident-current ( ) SQL Function: This […]
OLAP FUNCTIONS :- (I) It is also called as “Analytical function” (II) This is two types Rank Dense_rank (III) These two functions are used to calculate rank of a particular value from the set of values. Syntax :- Rank( ) over (set of values) Dense_rank() over [set of values] Display enames,salary,with rank ranking should be based […]
SQL Multi Row functions
MULTI ROW FUNCTIONS :– {NULL VALUE ARE NOT CALCULATED} These functions are also calld “Aggregate functions” (Or) Group functions. All these multi row functions will process multiple records. Applied on group of records and returns one value from the entire group. MAX ( ) Function in SQL:- Returns maximum value of a given expression. Syntax : Max(expr) […]
Group by Clause in SQL Server
Group by Clause :- group by clause is used to group records based on one (or) more columns to calculate Aggregates like max ,min,avg,sum etc. using group by we can produce summarized data from detailed data. Syntax :- Select <collist> from<tabname> [where<cond>] Group by <collist>[having<cond>] [order by <collist>] Display max salaries for each department Select deptno, […]
Having Clause in SQL Server
Having Clause :- Select deptno,max(sal),maxsal from emp where deptno in (10,30) group by deptno having max(sal)>=3000 Where vs having clause :— To filter data be fine group by use where clause To filter data after group by use having clause. In condition, if there is no aggregate function their use where clause. In condition if […]
Cube and Rollup in SQL Server
Cube and Rollup :- Both options are used to calculate sub_totals Select deptno,job,sum(sal) sumsal from emp group by deptno, job with rollup order by deptno,job Output :- Deptno job sumsal Null NULL 31025 → this is total salary of (hole) […]
Subqueries in SQL Server
SUB QUERIES :– A query embeded in another query is called “sub querry”. In this ,there will be two queries,one queries called inner-query and another query is called outer query. When it is executed,first inner query is executed then outer query The result of inner query act as input to outer query. Types of sub […]
Single Row Subquery in SQL Server
Single Row Subquery :- If inner query returns only one value, then the sub query is called “single row subquerry” Operations must be >, >=, <, <=, =, <>. Example :- Display employee records who is job equal to job of smith. Select * from emp where job = (select job from emp where ename= ‘smith’) Display […]
Multi row Subqueries in SQL Server
Multi Row Sub-Queries :– If inner query returns more than one value,then the sub query is called “multirow sub query” Syntax :- Select <collist>from<tabname> where colname operation (select statement) Here operation must be in, Not in, Any, All, Exists, not exists Example :- Display employee records whos job=job of Smith(or) job of blake. Select * from emp where job in (select […]
Nested Queries in SQL Server
Nested Queries in SQL Server :- A sub query embedded in another subquery is called “nested query”. Select → main query (select → sub query (select)) → Nested query Get the name of employee whos earning 2nd maximum salary. Select ename from emp where sal=(select max(sal)) from […]
Correlated Subqueries in SQL Server
Co-related sub-queries :- In co-related sub-queries execution starts from outer query for every record of outer query table Inner query is executed once. In co-related sub-queries inner query also takes value from outer query. Example :- Display employee records earning morethan avg salary of their dept Select * from emp a where a.sal>(select avg(sal) from emp where […]
Derived Tables in SQL Server
Derived tables :- This is introduced from SQL Server 2005. Sub queries follows from clause are called “Derived tables”. Syntax : Select * from (select statement) as alias. Use derived tables to simplify the complex processing. To use result of one query in another query. Example :- Select * from (select empno,ename,sal*12 annsal from emp) as e […]
Scalar Queries in SQL Server
SCALAR QUERIES :- subqueries follows select clause are called “scalar queries” Syntax :- Select (select statement) , (select statement) From<tabname> Example :- Select (select max(sal) from emp where deptno =10) D10, (select max(sal) from emp where deptno=20) D20, (select max(sal) from emp where deptno =30) D30 Output:- D10 D20 D30 […]
Database Transactions in SQL
DATABASE TRANSACTIONS :- A transaction is a unit of work,mainly consist of DML operations which must be made permanent as a whole and must be under as a whole. SQL server runs in 3 types of transaction modes :- Auto commit mode [default] Implicit transaction mode. Explicit transaction mode. (I) Auto commit mode :- {default} […]
Database Security in SQL Server
DATABASE SECURITY :- The different facilities in SQL Server to provide security :- Logins and users Great & revoke commands Views. (I) Logins & users :- Creating logins :- In sql server the users are two types : Window user Sql Server user (i) Creating windows user :- Open control panel […]
Grant Command in SQL with Example
GRANT COMMAND :– Grant command is used to be permission on a table to users. Syntax:- Grant <privilages> on <tabname> To <users> [with grant option] The privileges are: Select, insert, update, delete. Grant select,insert on emp to NIT. (This command is dbo is give permission to NIT table for select & insert) go to NIT table […]
Database Objects in SQL Server
DATABASE OBJECTS :- The different database objects are: Tables Views Synonyms Indexes Indexed views procedures Functions DB triggers VIEWS :- A view is a “ subset of a table” and also called “virtual table” (or) “stored query”. Views are mainly created for Security and Simplicity. By creating views, specific columns & specific rows can be […]
Simple Views in SQL
Simple and Complex Views in SQL:- Grant & Revoke Using grant & revoke we can restrict user operations. Views Using views we can restrict data [particular column] A view is based on one table is called “simple views”. Syntax :- Create view <name> [<with options>] as select statement [where<condition>] Example :- Select user_name –> Dbo Example of simple […]
Complex Views in SQL Server
Complex Views in SQL Server:- Views are based on more than one table and also based on Group by clause Having clause Distinct clause Aggregate function Expression. Then view is said to be “complex view”. Difference between simple view and complex view in sql: Simple view It is updatable {updatable means allow DML operations} complex […]
Encryption in SQL Server
Encryption in SQL Server :- If view created with encryption then view defination will be hidden in information_schema views table. Example :- create view v8 with encryption as select empno, ename, sal from emp […]
Indexes in SQL Server
Indexes in SQL Server :- Index is a database object used to make data retrieval fast. In database indexes are created on particular column. The column on which the index is created, it’s called “index-key”. index created on salary column looks as follows. In sql server, searching for a particular record is of two types. Table […]
Types of Indexes in SQL Server
Types of Indexes in SQL Server :- Simple index Composite index Unique index Non clustered index Clustered index To see sql server uses what type of scan ↓ To select the query ↓ Go to query option […]
Exporting and Importing Data in SQL Server
EXPORTING AND IMPORTING Data: The different facilities provided by sql server to do export & import. Export and import wizard Bcp [bulk copy program] Bulk insert
(I) SQL Server Export and Import Wizard :- Data is converted into file when we want to transfer data from one location to another. To do export : Select […]
Clustered and Non Clustered Indexes in SQL Server
TYPES OF INDEXES :- Clustered and non clustered indexes: Clustered and non clustered indexes. If order of the values in table and order of the values in index its not same,then index is called”non-cluster index”. By default SQL Server creates non clustered index. Clustered index : If order of the values in table and if order […]
Indexed Views in SQL Server
Indexed Views in SQL Server: A view whose query result is stored in database is called “indexed view” In oracle is called “materialized view”. Index views are created mainly for two reasons: To improve performance of aggregate operation. To create local copy for remote databases. These two operations are most expensive. Index views occupies memory as it […]
Backup and Restore in SQL Server
Backup and Restore in SQL Server : Backup in SQL Server:- To take backup of the database Select the database Right click Select task Select backup. Select the database : MYDB Backup : Full, Differential, Transaction log. Full → complete backup Deferential means backup since backup. Since last backup what new objects are copied in backup. Transaction log […]
T-SQL Programming in SQL Server
T-SQL Programming in SQL Server:- TSQL program is called “TSQL BLOCK”. TSQL Blocks are two types:- (I) Anonymous blocks (II) Named blocks: Procedures Functions DB triggers (I) Anonymous Blocks in SQL Server :- Declare statement : It is used to […]
Database Programming in SQL Server
Database Programming in SQL Server :- To do database programming SQL statements must be embedded in TSQL block. The following SQL statements can be embedded in TSQL block. (I) All DML commands (II) All TCL commands (III) DRL command Syntax of the select statement: Select @ var1=col1, @var2=col2 ——– from <tabname> [where<condition>] Example : Select @ eno=empno, @ename=ename from emp where empno=7369 […]
Control Statements in SQL Server
Control Statements in SQL Server : Conditional Statements in SQL Server :- (A) if <cond> Begin Statements End. Note :- If more than one statements […]
Cursors in SQL Server
Cursor in SQL Server :– A cursor is a pointer to resultset which contains set of records return by the select statement. Using the cursor we can process the record in resultset. cursor are used to process the multiple records. Cursor will reduce no. of trips to database server. To use cursor follow below steps :- […]
@@fetch_status in SQL Server
@@fetch_status in SQL Server :- This is one system variable which returns status of the fetch statement i:e fetch is successful or not. If fetch is successful returns 0, otherwise non-zero value. Write a program to display all employee names & salaries :- Declare c1 cursor for Select ename,sal from emp Declare […]
Scroll Cursor in SQL Server
Scroll Cursor in SQL Server :- Example :- Declare c1 cursor scroll for select statement By default cursors supports forward only scrolling and supports fetch next statement. If cursor is declared with scroll option it can fetch both forward & backward scrolling, and also supports all fetch statement like fetch next prior, first,last, absolute n, […]
Static and Dynamic Cursor in SQL Server
Static and Dynamic Cursor in SQL Server :- Static Cursor in SQL Server with Example : If the cursor is declared with static any changes make to the base table changes are not reflected to result-set. Example :- Emp […]
Local and Global Cursors
Local and Global Cursors :- If cursor is local then the cursor can access with in the program only. If cursor is global then the cursor can be declared in one progress can be used in another program. Global is default Program (I) :- Declare c1 cursor global […]
Sub-Programs in SQL Server
Sub-Programs in SQL Server :- Named blocks: Procedures Functions Db triggers procedures & functions are also called “sub-programs” subprograms means called in main program. Difference between Anonymous and named: Anonymous named Block without name anononymous blocks are saved in “operating system file” These blocks are not secured. These blocks can’t be re-used. Every time compile & executed. blocks […]
Procedures in SQL Server
Procedures in SQL Server :- A procedure is a pre-compiled named block stored in database that performs a task & may (or) mayn’t return a value. Procedures are stored in database, so it is called “stored procedures” Procedures are created to perform DML operations over database [transactions]. Syntax for creating procedure :– Create/alter procedures <name> → procedure declaration […]
Declaring Parameters with Default Values
Declaring parameters with default values :- If parameters are declared with default value at the time of calling procedure if the value is not passed to the parameter then default value assigned to that parameter. Example :-@x int =10 Example :- alter procedure Addnum (@x int,@y int=20,@z int output) As Begin Set @z=@x+@y End. Declare […]
Error Handling and Exception Handling in SQL Server
Error Handling and Exception handling in SQL Server:- An exception means “ Run time error” In SQL server if any statement causes run time error then SQL Server returns “error msg” and program execution is continued. When any error occurs to display user friendly message and to execute error processing statements and the error must be handled. To handle errors we […]
Functions in Exceptions Handling
Functions in exceptions handling : Error_number:- This function returns error number. Error_message:- It returns the error message. Error_line:- It returns the line number. Error_severity:- It returns error severity level. Error_state:- It returns error state level. Every error has got mainly 4 parts :- error_number error_message error_level error_state select * from sys.messages ↓ This table maintains the […]
User Defined Errors SQL Server
User Defined Errors SQL Server :- These errors are defined by user. These errors are raised by using the following statement Raiserror (error number/error message, error level, error state). Example :- Of user defined error : Create procedure div(@x int,@y int) As Begin […]
Difference between Procedures and Functions in SQL Server
Functions in SQL Server :- A function is also a named tsql block takes some input performs a task and must returns a value. Difference between Procedures and Functions in SQL Server: Procedures : A procedure need not return a value. Returns values using out parameters. procedure can return more than one value procedure can’t be […]
Function Types in SQL Server
Function Types in SQL Server :- It can be divided into two types Scalar valued Table valued Scalar Valued function in SQL Server:- These functions returns only one value of type int,varchar date time etc. Syntax :– Create/alter function <name>(parameters) Returns <type> As Begin Statements Return<exper> End. Example […]
Table Valued Functions in SQL Server
Table Valued Functions in SQL Server :- These functions returns set of records i:e table. The return type of this functions must be “table”. The return expression is ”select statement”. Syntax :- Create/alter function <name> (parameters) Returns table As Return(select statement). Example :- Create function getemp(@d int) Returns table […]
Database Triggers in SQL Server
Database Triggers in SQL Server:- A database trigger is also a ‘named block’ like procedures executed implicitly. Procedures in SQL Server : Called explicitly Procedure can be created without […]
DML Triggers in SQL Server
DML Triggers in SQL Server :- These triggers are created on particular table. These triggers are created to control DML operators. Syntax :- Create /alter trigger <name> Or<tablename>/<viewname> After/instead of insert,update,delete →triggers event As Begin Statements End. After triggers in SQL Server :- These triggers are fired (executed) after the DML operation is executed. INSTEAD […]
DDL Triggers in SQL Server
DDL Triggers in SQL Server :- It is introduced from sql server 2005. This triggers are created to control DDL operations like create,alter,drop. Syntax :- create trigger <name> On database After create ,alter,drop As Begin Statements End Example :- Create trigger trg10 On database After create create_table As […]
XML Integration in SQL Server
XML Integration in SQL Server :- Xml is simple and Unicode And platform independent. Transfer data from one file to another For each record one tag is created with name “row” Select * from emp for xml row Select * from emp For xml path. select * from emp for xml xpath root(‘emp’) Transfering data from […]
CLR Integration in SQL Server
CLR Integration in SQL Server :- The stored procedures & functions can also be created by using c# (or) VB. And the objects are called “CLR database objects” The difference is tsql is “procedure oriented”and c#,VB is “object oriented”. To create CLR database objects to follow the steps: create assembly in c# on VB. create […]
Locking in SQL Server
Locking in SQL Server :- Locking resolves of concurrent access where concurrent access means accessing same data by the number of users at same time. The following problem occurs concurrent access: dirty read last update Locks types are Two types: Shared lock -> It is denoted by (‘s’) exclusive lock -> it is denoted by ‘x’ Shared lock […]
Isolation Levels in SQL Server
Isolation Levels in SQL Server :- Locks are controlled by isolation levels, Types of isolation levels: Read uncommitted Read committed Serializable (I) Read uncommitted in SQL Server :- If the isolation level is set to read uncommitted then SQL Server doesn’t place any locks. (II) Read committed in SQL Server :- If the isolation level is set to read committed then shared locks are released immediately after […]