SQL Server Advance Concepts includes Stored Procedures, Views, triggers, Complex SQL Queries, Index, Performance Tunning, Database Mail Configuration, SQL Server Agent Job Scheduling and so on..
SQL Server IDENTITY_INSERT allows explicit values to be inserted into the identity column of a table.
The IDENTITY_INSERT statement must be set ON to insert explicit value for identity column. By default IDENTITY_INSERT is OFF.
SQL Server SET DATEFORMAT is used to set the order of the month, day, and year date parts for interpreting date character strings.
SQL Server SET XACT_ABORT controls the atomicity of any user defined transaction.
A Schema in SQL Server is a collection of database objects associated with a database.
A Recursive CTE (Common Table Expression) is a CTE that references itself. In doing so, the initial CTE is repeatedly executed, returning subsets of data, until it returns the complete result set.
CUBE operator is used to calculate subtotals and grand total for all combinations of grouping columns specified in the GROUP BY clause.
SQL GROUPING SETS allows computing multiple GROUP BY clauses in a single statement. The results of GROUPING SETS are the equivalent of UNION ALL of the specified groups.
SQL Server CTE (Common Table Expression) is temporary result set that is used to reference within another SELECT, INSERT, UPDATE, or DELETE statement.
A Sequence objects are used to sequentially generate numeric values that can be in ascending or descending order at a defined interval and may cycle if requested.
The Concept of User Defined table type and table valued parameters were introduced in SQL server 2008.
It fires in response to LOGON events of SQL Server is raised .
A LOGON Triggers can be useful in may ways, such as to restrict number of connection made by a Login or to prevent a user from logging-in outside .
SQL Server triggers are special kind of Stored Procedures that invokes whenever a special event in the database occurs.
SQL Server uses two virtual tables INSERTED and DELETED, whenever a trigger is called . These table is used to capture the data of the modified row before and after the event occurs tables , these tables are called as magic tables or virtual tables.
After Trigger (using FOR/AFTER CLAUSE)
This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.
The SQL Server Agent job is the specified action or series of actions that the SQL Server Agent will execute at any specific date time.
SQL Server allows us to create our functions called as user defined functions in SQL Server.
SQL SERVER ROLLUP clause is used to display subtotals and grand totals in the result set . It is always used with Group By clause.
SQL WAITFOR statement is used to blocks the execution of a batch statement, transnational statement or any stored procedure until a specified time or any time interval is reached, or a specified statement modifies or returns at least one row.
The TRY CATCH block is used to handle exceptions in Transact-SQL code .
