SQL 2016 Features News

1] Stretch Database

Stretch Database allows you to keep your most recent production data local to your location but migrate the historical data to the cloud(Microsoft Azure). This new feature gives you the benefit of presenting the data as a single data store while behind the scenes allowing you the flexibility to segregate active and historical data helping to reduce cost of storage, backups, and help speed up access to “Live” data.

2] Basic Availability Groups

Basic Availability Groups replaced Database Mirroring technology. This feature is available in Standard Edition giving you the ability to have a single primary and single replica database.

3] Distributed Availability Groups

Another new feature in SQL 2016 is the ability to have availability groups defined on two different Windows Clustered. This new feature allows available groups to exist in both the production environment as well as your disaster recovery (DR) environment. If connectivity to your DR site is interrupted, it will not affect your internal availability groups.

4] Query Store

You can now compare differences in your query plans over time without the need to create your own custom solution. SQL Server now stores query plans with executed queries and runtime statistics to make performance troubleshooting faster and easier.

5] Live Query Statistics

Gone are the days of viewing only the estimated execution plan or waiting for that long-running query to finish to see the actual execution plan. SQL Server now gives you the ability to view the execution plan as the query is active on the system.

6] In-Memory OLTP

First introduced in SQL 2014, In-Memory OLTP received several significant updates in SQL 2016. The ALTER TABLE command received a performance update to minimize the number of log writes to help reduce overall IO usage. Stats are now updated automatically. Additionally, several DDL and DML commands were added to your arsenal including Index on null values, foreign keys and DML triggers.

7] Changes to TempDB

New with 2016, when installing SQL Server, the installation wizard will detect the number of CPUs on the system and automatically populated the number of TempDB datafiles recommended to support the number of CPUs. The new wizard gives you more flexibility on configuring drive locations of the TempDB files. On high performance systems, Trace flags -T1117 and -T1118 no longer need to be set.

SQL – Trigger Disable in Database

In various interview questions asked below questions related to Disable Trigger.

1] How to disable the triggers for database?
2] How to disable all the triggers for all servers?

1]
DISABLE TRIGGER table_name ON database_name;
GO

2]
DISABLE TRIGGER ALL ON ALL SERVER;
GO

SQL – Index Rebuild / Index Reorganize

Index Rebuild will drop the existing Index and it will recreates the index.

ALTER INDEX index_name ON databasename.tablename REBUILD

Index Reorganize physically reorganizes the leaf nodes of the index.

ALTER INDEX index_name ON databasename.tablename REORGANIZE

Index should be rebuild when index fragmentation is most of the time great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.
Index rebuilding process uses more CPU and it acquired locking in database resources. In latest SQL Server version and Enterprise version there has option available i.e. ONLINE mode, through which you can be turned on when Index is rebuilt.
ONLINE option will keep index available during the rebuilding.

SQL – Search Text in Stored Procedure

There is below few questions which will ask you while Interview or Any time …
How can I find if particular table is being used in the stored procedure?
How to search in stored procedures?
How can I do dependency check for objects in stored procedure without using sp_depends?

The same feature can be implemented using following script.

–Searching the text or tableName in Stored Procedure
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%tableName or any text%’

SQL – Temp Table v/s Temp Variable

Temp Tables are originated for the storage and it’s manipulation of temporal data. Also its stored physically created in tempDB.
Temp Variables are originated for returning date-sets from table-valued functions.


Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.


Temp Table create using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.


Temp table result can be used by multiple users.
Table variable can be used by the current user only. 

Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,
Table variable won’t allow doing the DDL operations. But the table variable allows us to create the clustered index only.

Identify Locking SPs

In a previous section we learn about how to get Blocking SPs, In Todays section we seen how to identify locking .

Locking is integral part of successful transactions of RDBMS. It is important in SQL Server that transactions pass the ACID test:

  • Atomic – the transaction performs in an all-or-nothing fashion
  • Consistent
  • Isolated – transactions are properly isolated until they’re finished
  • Durable – the RDBMS will maintain a record of uncompleted transactions in the event of recovery during a failure. 

SQL – Unique Index and Unique Constraint

Unique Index and Unique Constraint both are the same in terms of working in Tables. They achieve same goal. Performance wise also both are same.

Unique Constraint
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE NONCLUSTERED(column_names) ON [PRIMARY]

Unique Index
CREATE UNIQUE NONCLUSTERED INDEX index_name ON table_name
(column_name) ON [PRIMARY]

In both syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.

How to Delete Duplicate Records from Table

Below query is useful to delete the duplicate Ids data from table. The table must have identity column, which will be used to identify the duplicate records.

CREATE TABLE StudentDetail(ID INT IDENTITY(1,1),StudentID INT,Name VARCHAR(10),Address VARCHAR(100))

INSERT INTO StudentDetail
SELECT 10001,’Test1′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10002,’Test2′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10003,’Test3′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10004,’Test4′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test5′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10001,’Test11′,’Mumbai’
INSERT INTO StudentDetail
SELECT 10005,’Test51′,’Mumbai’

;WITH CTE
AS
(
SELECT ID,ROW_NUMBER()OVER(PARTITION BY StudentID ORDER BY ID ASC)DuplID
FROM StudentDetail
)
DELETE FROM CTE WHERE DuplID > 1

SELECT * FROM StudentDetail;

SQL – DISTINCT and GROUP BY Difference

Distinct and Group By executed the same result as well as query plan of execution.
Group BY clause is used whenever aggregate functions are used.
Distinct use to remove the duplicates and it can’t be use in aggregate functions.

examples for DISTINCT
SELECT DISTINCT col1
FROM table1

examples for GROUP BY
SELECT col1
FROM table1
GROUP BY col1

both query will return the same output.

SQL – Find number Rows, Columns for each table in the database

From below query you will get the number of data present in each table of databases. Most consume table data.

–created the temp table to store the tables data size
CREATE TABLE #DBEachTable (
TableName sysname ,
RowCounts INT,
ReservedSize VARCHAR(50),
DataSize VARCHAR(50),
IndexSize VARCHAR(50),
UnusedSize VARCHAR(50))

–insert the tables data in created temp table
INSERT #DBEachTable
EXEC sp_msforeachtable ‘sp_spaceused ”?”’

–fetching the each individual tables data size
SELECT a.TableName,
a.RowCounts,
COUNT(*) AS ColumnCount,
a.DataSize
FROM #DBEachTable a
INNER JOIN information_schema.columns b
ON a.TableName collate database_default
= b.table_name collate database_default
GROUP BY a.TableName, a.RowCounts, a.DataSize
ORDER BY CAST(REPLACE(a.DataSize, ‘ KB’, ”) AS integer) DESC

Create your website at WordPress.com
Get started