samirbehara-zz

CREATE TABLE [dbo].[Titles](
	[Titlecode] [decimal](13, 0) NOT NULL,
	[ListCode] [char](3) NULL,
	[ZLineNumber] [int] NOT NULL,
	[ZLineRateCode] [char](3) NULL,
	[ZLineComment] [varchar](30) NULL,
 CONSTRAINT [pk_Titles] PRIMARY KEY CLUSTERED 
(
	[Titlecode] ASC,
	[ZLineNumber] ASC

samirbehara-zz

SELECT [t0].[Titlecode], [t0].[ZLineNumber]
FROM [Titles] AS [t0]
WHERE [t0].[ListCode] = @p0

samirbehara-zz

-- View the columns where the Masking function is applied

SELECT c.name 'ColumnName', tbl.name 'TableName', 
	   c.is_masked 'IsMasked', c.masking_function 'MaskingFunction'
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl 
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;

samirbehara-zz

CREATE TABLE [dbo].[snapshots](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[status] [NVARCHAR](4) NOT NULL,
	[islast] [BIT] NOT NULL,
	[version] [NVARCHAR](500) NULL,
	[purge_status] [INT] NULL,
	[period1_mode] [NVARCHAR](100) NULL,
	[period1_param] [NVARCHAR](100) NULL,
	[period2_mode] [NVARCHAR](100) NULL,

samirbehara-zz

CREATE TABLE [dbo].[snapshots](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[status] [NVARCHAR](4) NOT NULL,
	[islast] [BIT] NOT NULL,
	[version] [NVARCHAR](500) NULL,
	[purge_status] [INT] NULL,
	[period1_mode] [NVARCHAR](100) NULL,
	[period1_param] [NVARCHAR](100) NULL,
	[period2_mode] [NVARCHAR](100) NULL,

samirbehara-zz

-- Modify your existing masked columns
ALTER TABLE dbo.Customer
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"X-XXX-X",1)')

-- View the unmasked data in Customer table
SELECT * FROM dbo.Customer;

samirbehara-zz

--Demonstration of COMPRESS and DECOMPRESS Features
SELECT COMPRESS ('New Features of SQL Server 2016')

SELECT CAST(DECOMPRESS(0x1F8B0800000000000400F34B2D57704B4D2C292D4A2D56C84F53080EF451084E2D2A4B2D52303230340300FD90F96A1F000000) AS VARCHAR(MAX))

samirbehara-zz

-- String splitting function
SELECT * FROM STRING_SPLIT('SQL SERVER 2016 ROCKS', ' ')

samirbehara-zz

DROP TABLE IF EXISTS dbo.EmployeeSkills


--Create a sample table
CREATE TABLE dbo.EmployeeSkills
(  
   EmployeeID		INT IDENTITY PRIMARY KEY,
   FirstName		VARCHAR(50),
   Email            VARCHAR(50),
   Skills           VARCHAR(100) 

samirbehara-zz

SELECT   DISTINCT CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage] ,
         migs.last_user_seek ,
         mid.[statement] AS [Database.Schema.Table] ,
         mid.equality_columns ,
         mid.inequality_columns ,
         mid.included_columns ,
         migs.unique_compiles ,
         migs.user_seeks ,
         migs.avg_total_user_cost ,
         migs.avg_user_impact ,