Home » SQL SERVER » SQL Advance »
SQL Advance
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 Dirty Reads in concurrent transactions
 SQL Server Dirty Reads problems in concurrent transactions
SQL Server Dynamic PIVOT
SQL server allows us to transform a row- level data into columnar data using SQL Pivot.
SQL CLUSTERED INDEX
SQL Server Clustered index
A clustered index defines the order in which data is physically stored in a table. A table can have only one clustered index because data rows can be only sorted in one order.
How to get all tables those are having a primary key
How to get all the tables in database those are having primary key constraints ?
SQL Server TEMPORAL TABLE
SQL Server Temporal tables (system-versioned tables) allow us to track data changes. It was introduced in SQL Server 2016.
It allows SQL Server to maintain and manage the history of the data in the table automatically, So we can get all information about the data that was stored at any specified time rather than just the data that is current.
SQL SERVER IF..ELSE
SQL Server IF..ELSE block is a control-of-flow that allows you to execute or skip a statement block based on a specified condition. Â
SYNTAX
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE { sql_statement | statement_block } ]Â Â
SYNTAX
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE { sql_statement | statement_block } ]
SQL Server WHILE
SQL Server WHILE loop sets a condition for the repeated execution of an SQL statement or statement block.
SQL Server UNPIVOT
SQL Server UNPIVOT allow us to transform columns of a table-valued expression into column values that is rotating columns into rows.
It performs a reverse operation of SQL Server PIVOT.
SYNTAX
SELECT …
FROM …
UNPIVOT (
FOR
[ … ]
SQL Server FOR JSON Clause
SQL Server FOR JSON Clause
SQL Server FOR JSON Clause is used to format query result to JSON format or export data from SQL Server as JSON format. It was introduced in SQL Server 2016 version.
SQL Server OFFSET FETCH
SQL Server OFFSET FETCH clauses are used to set the limit to number of rows returned by a query. Â
SQL Server @@SPID
SQL Server @@SPID
SQL Server @@SPID is a SQL Server configuration function that is used to return the session id of current user process.
SYNTAX
@@SPID
SQL Server @@Version
SQL Server @@Version is a configuration function that returns system and build information for the current installation of SQL Server. SYNTAX @@Version Return Type of… Read More »SQL Server @@Version
SQL SERVER SP_MONITOR
SP_MONITOR is a SQL Server Database Engine system stored procedure which is used to Display statistics about Microsoft SQL Server.
SYNTAX
EXEC sp_monitor
SP_RENAME in SQL Server
SQL Server SP_NAMEÂ is SQL Server Database Engine stored procedure that is used to Changes the name of a user-created object in the current database. This object can be a table, index column or alias datatype.
How to send a SQL Server query results in e-mail message
How to send a SQL Server Query result in e-mail message?
If we have configured SQL Server Database Mail then we can easily send query result as an attachment in mail body.
Troubleshooting Database Mail in SQL Server
Troubleshooting Database Mail
If you are not getting Database Mail after configuring it , you need to got through few more step to fix it.
SQL Server Database Mail Configurations
SQL Server Database Mail is used to send e-mail from the SQL Server Database Engine to user. We can also send an output of any stored procedures or table records in e-mail as an excel attachment or in HTML format in e-mail body.
Identifying Object Dependencies in SQL Server
In SQL Server there are new Dynamic Management Functions that is used to keep track of Object Dependencies sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.
SQL Server Synonyms
A synonym is a database object that allows an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.