Helpful Visual Studio Extensions 5

SQL Search

Works with Visual Studio 2015, 2017, 2019

Speed up SQL Server database development by finding SQL objects fast in Visual Studio. 

Redgate SQL Search is a free extension for Visual Studio to quickly search for fragments of SQL across databases, and easily navigate to those objects. This saves time and makes teams more productive, so you can get back to the task in hand. 

  • Find fragments of SQL in tables, views, stored procedures, functions, views, jobs, and more 
  • Quickly navigate to objects wherever they happen to be on a server 
  • Search across multiple object types and multiple databases 
  • Find all references to an object 
  • Search with booleans and wildcards 
  • Work in either Visual Studio or SQL Server Management Studio

More Details: https://marketplace.visualstudio.com/items?itemName=vs-publisher-306627.RedgateSQLSearch

Helpful Visual Studio Extensions 4

SQL Prompt Core

Works with Visual Studio 2017 Enterprise

With SQL Prompt Core you can:

  • Save keystrokes with code completion for T-SQL commands and keywords as you type
  • Easily complete JOIN statements with suggestions for JOIN conditions based on foreign key relationships or column similarities
  • Speed up SQL coding with autocompletion for INSERT and EXEC statements
  • Autocomplete based on mid-string matching and CamelCase suggestions, so you don’t have to remember every object name
  • Avoid bad practice – expand wildcards to show the full column list so you only retrieve the data you need

More Details: https://marketplace.visualstudio.com/items?itemName=vs-publisher-306627.RedgateSQLPromptCore

Basic PIVOT SQL query

SQL Query

DECLARE @tbl TABLE
(
Id INT,
Name VARCHAR(100),
Year INT,
Amount INT
)

INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(1, ‘Alex’, 2000, 2010)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(1, ‘Alex’, 2001, 2020)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(1, ‘Alex’, 2002, 2030)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(1, ‘Alex’, 2003, 2040)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(2, ‘Russell’, 2002, 2010)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(2, ‘Russell’, 2003, 2020)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(2, ‘Russell’, 2004, 2030)
INSERT INTO @tbl(Id, Name, Year, Amount) VALUES(2, ‘Russell’, 2005, 2040)

SELECT * FROM @tbl

Basic Output

SELECT Name, [2000], [2001], [2002], [2003], [2004], [2005]
FROM (SELECT * FROM @tbl) AS P
PIVOT (
SUM(Amount) FOR Year IN ([2000] , [2001], [2002], [2003], [2004], [2005])
) AS PT

PIVOT Output

New features in SQL 2016

AT TIME ZONE:

Converts an inputdate to the corresponding datetimeoffset value in the target time zone.

SELECT GETDATE() [Current Time]
UNION ALL
SELECT CONVERT(datetimeoffset, GETDATE()) AT TIME ZONE ‘Central European Standard Time’
UNION ALL
SELECT CONVERT(datetimeoffset, GETDATE()) AT TIME ZONE ‘Pacific Standard Time’

For more details: https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017

STRING_SPLIT

A. Split comma-separated value string

DECLARE @string VARCHAR(800) = ‘001,002,003,004’
SELECT * FROM STRING_SPLIT (@string,’,’)

B. Split comma-separated value string in a column

Product table has a column with comma-separate list of tags shown in the following example:

ProductId Name Tags
1 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

Following query transforms each list of tags and joins them with the original row:

SELECT ProductId, Name, value
FROM Product
CROSS APPLY STRING_SPLIT(Tags, ‘,’);

Here is the result set.

ProductId Name value
1 Full-Finger Gloves clothing
1 Full-Finger Gloves road
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

For more details: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

DROP IF EXISTS

A new syntax has been introduced to check if an object exists before dropping it. Previously, if you wanted to check if a table existed before you dropped it, you had to write a statement like this:

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestDrop]’) AND [type] IN (N’U’))
DROP TABLE [dbo].[TestDrop];

With the new syntax, this shortens to the following statement:

DROP TABLE IF EXISTS [dbo].[TestDrop];

JSON support

Similar like XML, SQL Server now supports the JSON format. You can for example convert tabular data to JSON using the FOR JSON clause. An example:

You can also read JSON data and convert it to tabular data by using OPENJSON. There are also built-in support functions:

  • ISJSON – test a string to see if it contains valid JSON
  • JSON_VALUE – extract a scalar value from a JSON string
  • JSON_QUERY – extracts an object or an array from a JSON string

Row Level Security

This is one of the security features of SQL 2016. It allows you to secure your data row wise, in short you can define a row, that will be viewed by a particular SQL user only. So depending upon the SQL user access permission, we can restrict row level data, e.g., we can ensure if employees can view only their department data though department table is the same.

To implement Row level security, you need to define Security policy with a predicate and function.Security policy: We need to create a policy for security, here is simple syntax:

CREATE SECURITY POLICY fn_security ADD [FILTER | BLOCK] PREDICATE FunctionName ON TableName

For more detail: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017