Query Store In SQL Server 2016


                 SQL Server Query Store is a relatively new feature introduced in SQL Server 2016.  This will helps you to  identifying performance problems in the SQL Server. Query Store basically captures and stores the history of query execution plans and its performance data.


Query store is a database level feature, which is disabled by default. To enable this follow below steps.
  • Run the below Query to Enable Query Store against database.
               ALTER DATABASE [DataBaseName]  
               SET QUERY_STORE = ON

  •  Run the below To Desable and clear Query Store 
               ALTER DATABASE [DatabaseName]  
               SET QUERY_STORE CLEAR;

  • The following script sets a new Max Size. 
              ALTER DATABASE [DatabaseName]
              SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024)

  • To set different value for Data Flush Interval.
             ALTER DATABASE [DatabaseName]
             SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900)

  • To Set Statistical Collection Intervel.
             ALTER DATABASE [DataBaseName]
             SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60)
  • To Set Time based cleanup policy.
            ALTER DATABASE [DatabaseName]
            SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90))

  • To set size based auto cleanup.
           ALTER DATABASE [DatabaseName]
           SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

  
 Once you Enabled Query Store you can see Query Store Folder Under Database




     Open the Top Resource Consumed Query, You can see the SQL Server start itself to analyze Query performance with execution plan





Rename Table In SQL Server

How to Rename table In SQL Server 

 

       In SQL Server there no direct query to rename table. It can be possible using built in procedure in SQL server.

The following syntax help you to raname a table using procedure.

      EXEC sp_rename 'old_table_name', 'new_table_name' 


Rename table Using SSMS

     Another way to rename a table is Rght Click the table from  SQL Server Management Studio.

 

 

 

Using Calculated Column In SQL Server


 
     Calculated columns can add great flexibility in database design .We can apply formula while creating tables to get computed values.below example help you to create computed column in sqlsever.

1. Create a temp table with calculated columns. Here column Total is the calculated one. It is populated sum of Mark1 and Mark2

CREATE TABLE #TempCalculatedTable(
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[Mark1] [int] NULL,
[Mark2] [int] NULL,
[Total] AS (ISNULL([Mark1],0)+ISNULL([Mark2],0)) /*Computed Column*/
)

2. Inserting data to temp table. Here we are inserting ID, Name, Mark1,Mark2 to temp table.

INSERT INTO #TempCalculatedTable
SELECT 1,'Jack',1000,2000
UNION ALL
SELECT 2,'Martin',2000,3000
UNION ALL
SELECT 3,'Stella',2000,3000
UNION ALL
SELECT 3,'Stella',NULL,3000
UNION ALL
SELECT 3,'Stella',NULL,NULL

3. Selecting data from temp Table. Here we can see calculated column Total populated automatically against each row.

SELECT *
FROM #TempCalculatedTable






Using iif in SQL server

IIF() is the new built-in logical function introduced as a Part of Sql Server 2012 .we can replace CASE and IF...ELSE statement using IIF().
Syntax: IIF ( boolean_expression, true, false )
If Boolean expression evaluates to TRUE then Second argument will be the result otherwise Third argument will be the result
/*Checking MALE or Female Using Case */

DECLARE @Gender INT = 1

SELECT CASE WHEN @Gender = 1 THEN 'MALE' ELSE 'FEMALE' END AS Gender

Result : MALE


/*Checking MALE or Female Using IF ELSE */

DECLARE @Gender INT = 1

IF(@Gender = 1)
   BEGIN
    SELECT 'Male' Gender
   END
ELSE
   BEGIN
    SELECT 'Female' Gender
   END

Result : MALE

/*Checking MALE or Female Using IIF */

DECLARE @Gender INT = 1

SELECT iif(@Gender = 1, 'MALE','FEMALE') AS Gender

Result : MALE

/*Checking Multivalue using IIF */

DECLARE @NAME Varchar(50) = 'SQL1'

SELECT IIF(@NAME IN ('SQL','ORACLE'),
'DATABASE', 'NOTE IN LIST' )

Result : NOTE IN LIST