SELECT CHOOSE(2, 'Female', 'Male', 'Unknown') AS [Gender];

SELECT CHOOSE(4, 'Female', 'Male', 'Unknown') AS [Gender];

SELECT TOP 5
    DATEPART(DD, l.[Date]) AS [Date],
    CHOOSE(DATEPART(MM, l.[Date]), 'January', 'February', 'March', 'April', 'May', 'June', 
                                   'July', 'August', 'September', 'October', 'November', 
                                   'December') [Month],
    DATEPART(YYYY, l.[Date]) AS [Year]
FROM
    dbo.[Log] AS l
ORDER BY
    l.Id DESC;

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

SELECT CONVERT(INT, id) AS IntegerValue, somevalue FROM dbo.TestTry

SELECT CONVERT(INT, id) AS IntegerValue, somevalue FROM dbo.TestTry WHERE ISNUMERIC(id) = 1;

SELECT 
    CONVERT(INT, id) AS IntegerValue, somevalue 
FROM 
    dbo.TestTry 
WHERE 
    ISNUMERIC(TRY_PARSE(id AS int)) = 1; 

DECLARE @d DATETIME = '05/01/2015';
SELECT 
    FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result',
    FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result',
    FORMAT ( @d, 'D', 'de-de' ) AS 'German Result',
    FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result'; 

DECLARE @dp FLOAT = 34.5
SELECT 
    FORMAT(@dp, '0.0') AS OneDecimalPlace,
    FORMAT(@dp, '00') AS NoDecimalPlace,
    FORMAT(@dp, '0000') AS PaddedNoDecimal,
    FORMAT(@dp, '0000.00') AS PaddedDecimalPlace

DECLARE @t FLOAT = 68    
SELECT FORMAT(@t, '# "degrees"') AS [Degrees]

DECLARE @num FLOAT = 012.30
SELECT FORMAT(@num, '###.##') AS NumberFormat

DECLARE @phonenumber INT = 1234567890
SELECT 
    FORMAT(@phonenumber, '(###) ###-####') AS PhoneNumberFormat1,
    FORMAT(@phonenumber, '###-###-####') AS PhoneNumberFormat2,
    FORMAT(@phonenumber, '###\.###\.####') AS PhoneNumberFormat3

CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH 1
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE 
GO

DECLARE @NextSequence int
SET  @NextSequence = NEXT VALUE FOR dbo.LogSequence;

INSERT INTO dbo.LogTable (LogId, LogValue)
VALUES (@NextSequence, 
       '@NextSequence will have a value of '+ CAST(@NextSequence AS VARCHAR) + '.')

SELECT current_value FROM sys.sequences WHERE name = 'LogSequence';

SELECT current_value FROM sys.sequences WHERE name = 'TicketSequence';

INSERT INTO dbo.ServiceTickets (Department) VALUES ('M')

CREATE TABLE dbo.ServiceTickets
(
 TicketID INT PRIMARY KEY CLUSTERED DEFAULT NEXT VALUE FOR TicketSequence,
 Department CHAR(1) NOT NULL CHECK (Department IN ('M', 'F'))
) ON [FGData];

CREATE SEQUENCE [dbo].[TicketSequence] 
 AS [INT]
 START WITH -2147483648
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE 
GO

CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH 1
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE
 NO CYCLE
GO

CREATE SEQUENCE [dbo].[LogSequence] 
 AS [INT]
 START WITH 1
 INCREMENT BY 1
 -2147483648 MINVALUE
 MAXVALUE 2147483647
 CACHE 50
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCS_Master] ON [dbo].[LogCS]
(
    [Id],
    [Date],
    [Thread],
    [Level],
    [Logger],
    [NestedContext],
    [Message],
    [Exception],
    [HostName],
    [Thread_Context_ID]
)WITH (DROP_EXISTING = OFF) ON [FGCS]
GO

ALTER TABLE [dbo].[LogCS] REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE)
GO

SELECT COUNT(*) AS TotalTableCount FROM dbo.Log;

Table 'Log'. Scan count 5, logical reads 40778, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 2
Milliseconds To Execute: 1826

Clustered Column Store Index
Table 'LogCS'. Scan count 5, logical reads 135, physical reads 0, read-ahead reads 0, 
lob logical reads 718, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 0
Milliseconds To Execute: 46

SELECT COUNT(*) AS TotalTableCount FROM dbo.LogCS;

SELECT COUNT(*) AS LoggerCount FROM dbo.Log WHERE Logger = 'some value';

No Index
Table 'Log'. Scan count 5, logical reads 40850, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 2
Milliseconds To Execute: 2040

Non-Clustered Column Store Index
Table 'LogCS'. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 410, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 0
Milliseconds To Execute: 106

SELECT COUNT(*) AS LoggerCount FROM dbo.LogCS WHERE Logger = 'some value';

SELECT COUNT(*) AS LevelCount FROM dbo.Log WHERE Level = 'Error';

Non-Clustered Index
Table 'Log'. Scan count 5, logical reads 18306, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 1
Milliseconds To Execute: 510

Non-Clustered Column Store Index
Table 'LogCS'. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 390, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Seconds To Execute: 0
Milliseconds To Execute: 56

SELECT COUNT(*) AS LevelCount FROM dbo.LogCS WHERE Level = 'Error';

DECLARE @date DATETIME = GETDATE();
SELECT
    EOMONTH(@date) AS 'This Month',
    EOMONTH(@date, 1) AS 'Next Month',
    EOMONTH(@date, -1) AS 'Last Month';
GO

DECLARE	@emails VARCHAR(400) = 'test@test.com,testing@mywebsite.com,demo@test.com';   
SELECT
	value
FROM
	STRING_SPLIT(@emails, ',');  

SELECT
	value
FROM
	STRING_SPLIT('My random sentance for this demo.', ' ');

SELECT 
	IndustryID, 
	IndustryName
FROM 
	dbo.Industries  
	INNER JOIN STRING_SPLIT('1,3,5,7,9,10,14,27,74,86', ',')   
		ON value = IndustryID;

DECLARE @StartDate AS DATETIME2(0) = '1/1/2016', @EndDate AS DATETIME2(0) = GETDATE();
--DECLARE @StartDate AS DATETIME2(0) = DATEADD(YEAR,-19,GETDATE()), @EndDate AS DATETIME2(0) = GETDATE();
SELECT 
	DATEDIFF_BIG(YEAR, @StartDate, @EndDate) AS [Year],
	DATEDIFF_BIG(quarter, @StartDate, @EndDate) AS [Quarter],
	DATEDIFF_BIG(month, @StartDate, @EndDate) AS [Month],
	DATEDIFF_BIG(dayofyear, @StartDate, @EndDate) AS [DayOfYear],
	DATEDIFF_BIG(day, @StartDate, @EndDate) [Day],
	DATEDIFF_BIG(week, @StartDate, @EndDate) AS [Week],
	DATEDIFF_BIG(hour, @StartDate, @EndDate) AS [Hour],
	DATEDIFF_BIG(minute, @StartDate, @EndDate) AS [Minute],
	DATEDIFF_BIG(second, @StartDate, @EndDate) AS [Second],
	DATEDIFF_BIG(millisecond, @StartDate, @EndDate) AS [Millisecond];

DROP PROCEDURE IF EXISTS dbo.MySP;

DROP INDEX IF EXISTS IX_MyTestTable_Index ON dbo.MyTestTable;

DROP TABLE IF EXISTS dbo.MyTestTable;

DROP SYNONYM IF EXISTS dbo.MySynonym;

DROP TRIGGER IF EXISTS dbo.MyTrigger;

DROP USER IF EXISTS [user_name];

DROP VIEW IF EXISTS dbo.MyView;

DROP TYPE IF EXISTS dbo.MyType;

DROP SCHEMA IF EXISTS Demo;

DROP FUNCTION IF EXISTS dbo.MyFunction;

ALTER TABLE dbo.MyTestTable DROP COLUMN IF EXISTS MyTestColumn;

ALTER TABLE dbo.MyTestTable DROP CONSTRAINT IF EXISTS PK_MyTestTable;

[  {
    "AccountId":5638794,
    "DisplayName":"RiaDess",
    "CreationDate":"2015-01-17T13:34:58",
    "LastAccessDate":"2015-03-07T23:51:32.930",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":2348349,
    "DisplayName":"Ed Cottrell",
    "Age":36,
    "Location":"Houston, TX",
    "CreationDate":"2013-10-30T05:47:17",
    "LastAccessDate":"2015-03-07T16:02:03.720",
    "Reputation":103,
    "DownVotes":0,
    "UpVotes":0,
    "Views":1
  },
  {
    "AccountId":384624,
    "DisplayName":"THelper",
    "Location":"The Netherlands",
    "CreationDate":"2011-12-22T08:24:28",
    "LastAccessDate":"2015-03-07T15:19:56.533",
    "Reputation":2065,
    "DownVotes":0,
    "UpVotes":54,
    "Views":6
  },
  {
    "AccountId":45818,
    "DisplayName":"Adam",
    "CreationDate":"2011-07-16T13:39:07",
    "LastAccessDate":"2015-03-06T22:22:21.207",
    "Reputation":1264,
    "DownVotes":0,
    "UpVotes":2,
    "Views":1
  },
  {
    "AccountId":3074278,
    "DisplayName":"Biotechnologist",
    "CreationDate":"2014-01-21T23:49:45",
    "LastAccessDate":"2015-03-04T11:58:03.593",
    "Reputation":106,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":5080390,
    "DisplayName":"Sue",
    "Location":"Central Massachusetts",
    "CreationDate":"2015-02-04T18:58:56",
    "LastAccessDate":"2015-03-04T02:28:21.943",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":0
  },
  {
    "AccountId":42117,
    "DisplayName":"Pops",
    "Age":64,
    "Location":"East Coast, United States",
    "CreationDate":"2013-08-26T15:35:51",
    "LastAccessDate":"2015-03-03T21:23:23.593",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":3
  },
  {
    "AccountId":178712,
    "DisplayName":"mehta",
    "Location":"India",
    "CreationDate":"2013-02-04T08:14:42",
    "LastAccessDate":"2015-03-03T19:26:11.760",
    "Reputation":301,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":1275707,
    "DisplayName":"kircm",
    "Age":43,
    "Location":"New York",
    "CreationDate":"2012-11-18T22:39:24",
    "LastAccessDate":"2015-03-03T16:55:49.090",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountId":4521361,
    "DisplayName":"Enthusiastic Student",
    "Age":27,
    "CreationDate":"2014-05-25T21:16:30",
    "LastAccessDate":"2015-03-03T08:54:19.967",
    "Reputation":145,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  }]

[  {
    "AccountNumber":5638794,
    "DisplayName":"RiaDess",
    "CreationDate":"2015-01-17T13:34:58",
    "LastAccessDate":"2015-03-07T23:51:32.930",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":2348349,
    "DisplayName":"Ed Cottrell",
    "Age":36,
    "Location":"Houston, TX",
    "CreationDate":"2013-10-30T05:47:17",
    "LastAccessDate":"2015-03-07T16:02:03.720",
    "Reputation":103,
    "DownVotes":0,
    "UpVotes":0,
    "Views":1
  },
  {
    "AccountNumber":384624,
    "DisplayName":"THelper",
    "Location":"The Netherlands",
    "CreationDate":"2011-12-22T08:24:28",
    "LastAccessDate":"2015-03-07T15:19:56.533",
    "Reputation":2065,
    "DownVotes":0,
    "UpVotes":54,
    "Views":6
  },
  {
    "AccountNumber":45818,
    "DisplayName":"Adam",
    "CreationDate":"2011-07-16T13:39:07",
    "LastAccessDate":"2015-03-06T22:22:21.207",
    "Reputation":1264,
    "DownVotes":0,
    "UpVotes":2,
    "Views":1
  },
  {
    "AccountNumber":3074278,
    "DisplayName":"Biotechnologist",
    "CreationDate":"2014-01-21T23:49:45",
    "LastAccessDate":"2015-03-04T11:58:03.593",
    "Reputation":106,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":5080390,
    "DisplayName":"Sue",
    "Location":"Central Massachusetts",
    "CreationDate":"2015-02-04T18:58:56",
    "LastAccessDate":"2015-03-04T02:28:21.943",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":0
  },
  {
    "AccountNumber":42117,
    "DisplayName":"Pops",
    "Age":64,
    "Location":"East Coast, United States",
    "CreationDate":"2013-08-26T15:35:51",
    "LastAccessDate":"2015-03-03T21:23:23.593",
    "Reputation":101,
    "DownVotes":0,
    "UpVotes":2,
    "Views":3
  },
  {
    "AccountNumber":178712,
    "DisplayName":"mehta",
    "Location":"India",
    "CreationDate":"2013-02-04T08:14:42",
    "LastAccessDate":"2015-03-03T19:26:11.760",
    "Reputation":301,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":1275707,
    "DisplayName":"kircm",
    "Age":43,
    "Location":"New York",
    "CreationDate":"2012-11-18T22:39:24",
    "LastAccessDate":"2015-03-03T16:55:49.090",
    "Reputation":1,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  },
  {
    "AccountNumber":4521361,
    "DisplayName":"Enthusiastic Student",
    "Age":27,
    "CreationDate":"2014-05-25T21:16:30",
    "LastAccessDate":"2015-03-03T08:54:19.967",
    "Reputation":145,
    "DownVotes":0,
    "UpVotes":0,
    "Views":0
  }]

[
{"accountid":45818,"Posts":[{}]},
{"accountid":384624,
    "Posts":[
        {"Id":192,"PostTypeId":1,"Title":"Why is there an \"untagged\" tag?"},
        {"Id":222,"PostTypeId":1,"Title":"Typo in 'self-managment' tag"},
        {"Id":230,"PostTypeId":1,"Title":"Merge software tag and application tag?"},
        {"Id":250,"PostTypeId":1,"Title":"How do we get this site out of beta?"},
        {"Id":323,"PostTypeId":2,"Title":"<p>I've decided to start keeping track of the PP s"},
        {"Id":346,"PostTypeId":1,"Title":"Remove [productivity] tag once again?"}]},
{"accountid":1275707,"Posts":[{}]},
{"accountid":178712,"Posts":[{}]},
{"accountid":42117,"Posts":[{}]},
{"accountid":2348349,"Posts":[{}]},
{"accountid":3074278,"Posts":[{}]},
{"accountid":4521361,"Posts":[{}]},
{"accountid":5638794,"Posts":[{}]},
{"accountid":5080390,"Posts":[{}]}
]

{"name":"John","skills":["C#","SQL"]}
{"name":"Mike","skills":["C#","SQL"]}
{"name":"Mike","skills":["C#","SQL"],"surname":"Smith"}
{"skills":["C#","SQL"],"surname":"Smith"}
{"skills":["C#","SQL","Azure"],"surname":"Smith"}

{"name":"John","skills":["C#","SQL"]}
{"name":"Mike","skills":["C#","SQL","Azure"],"surname":"Smith"}
{"price":49.99}
{"Price":49.99}
{"click_count": 173}
{"click_count": 174}

DECLARE @jsontext VARCHAR(MAX) = '[{"name": "Åland Islands", "code": "AX"},{"name": "Albania", "code": "AL"},{"name": "Algeria", "code": "DZ"},{"name": "American Samoa", "code": "AS"},{"name": "AndorrA", "code": "AD"},{"name": "Angola", "code": "AO"},{"name": "Anguilla", "code": "AI"},{"name": "Antarctica", "code": "AQ"},{"name": "Antigua and Barbuda", "code": "AG"},{"name": "Argentina", "code": "AR"},{"name": "Armenia", "code": "AM"},{"name": "Aruba", "code": "AW"},{"name": "Australia", "code": "AU"},{"name": "Austria", "code": "AT"},{"name": "Azerbaijan", "code": "AZ"},{"name": "Bahamas", "code": "BS"},{"name": "Bahrain", "code": "BH"},{"name": "Bangladesh", "code": "BD"},{"name": "Barbados", "code": "BB"},{"name": "Belarus", "code": "BY"},{"name": "Belgium", "code": "BE"},{"name": "Belize", "code": "BZ"},{"name": "Benin", "code": "BJ"},{"name": "Bermuda", "code": "BM"},{"name": "Bhutan", "code": "BT"},{"name": "Bolivia", "code": "BO"},{"name": "Bosnia and Herzegovina", "code": "BA"},{"name": "Botswana", "code": "BW"},{"name": "Bouvet Island", "code": "BV"},{"name": "Brazil", "code": "BR"},{"name": "British Indian Ocean Territory", "code": "IO"},{"name": "Brunei Darussalam", "code": "BN"},{"name": "Bulgaria", "code": "BG"},{"name": "Burkina Faso", "code": "BF"},{"name": "Burundi", "code": "BI"},{"name": "Cambodia", "code": "KH"},{"name": "Cameroon", "code": "CM"},{"name": "Canada", "code": "CA"},{"name": "Cape Verde", "code": "CV"},{"name": "Cayman Islands", "code": "KY"},{"name": "Central African Republic", "code": "CF"},{"name": "Chad", "code": "TD"},{"name": "Chile", "code": "CL"},{"name": "China", "code": "CN"},{"name": "Christmas Island", "code": "CX"},{"name": "Cocos (Keeling) Islands", "code": "CC"},{"name": "Colombia", "code": "CO"},{"name": "Comoros", "code": "KM"},{"name": "Congo", "code": "CG"},{"name": "Congo, The Democratic Republic of the", "code": "CD"},{"name": "Cook Islands", "code": "CK"},{"name": "Costa Rica", "code": "CR"},{"name": "Cote D"Ivoire", "code": "CI"},{"name": "Croatia", "code": "HR"},{"name": "Cuba", "code": "CU"},{"name": "Cyprus", "code": "CY"},{"name": "Czech Republic", "code": "CZ"},{"name": "Denmark", "code": "DK"},{"name": "Djibouti", "code": "DJ"},{"name": "Dominica", "code": "DM"},{"name": "Dominican Republic", "code": "DO"},{"name": "Ecuador", "code": "EC"},{"name": "Egypt", "code": "EG"},{"name": "El Salvador", "code": "SV"},{"name": "Equatorial Guinea", "code": "GQ"},{"name": "Eritrea", "code": "ER"},{"name": "Estonia", "code": "EE"},{"name": "Ethiopia", "code": "ET"},{"name": "Falkland Islands (Malvinas)", "code": "FK"},{"name": "Faroe Islands", "code": "FO"},{"name": "Fiji", "code": "FJ"},{"name": "Finland", "code": "FI"},{"name": "France", "code": "FR"},{"name": "French Guiana", "code": "GF"},{"name": "French Polynesia", "code": "PF"},{"name": "French Southern Territories", "code": "TF"},{"name": "Gabon", "code": "GA"},{"name": "Gambia", "code": "GM"},{"name": "Georgia", "code": "GE"},{"name": "Germany", "code": "DE"},{"name": "Ghana", "code": "GH"},{"name": "Gibraltar", "code": "GI"},{"name": "Greece", "code": "GR"},{"name": "Greenland", "code": "GL"},{"name": "Grenada", "code": "GD"},{"name": "Guadeloupe", "code": "GP"},{"name": "Guam", "code": "GU"},{"name": "Guatemala", "code": "GT"},{"name": "Guernsey", "code": "GG"},{"name": "Guinea", "code": "GN"},{"name": "Guinea-Bissau", "code": "GW"},{"name": "Guyana", "code": "GY"},{"name": "Haiti", "code": "HT"},{"name": "Heard Island and Mcdonald Islands", "code": "HM"},{"name": "Holy See (Vatican City State)", "code": "VA"},{"name": "Honduras", "code": "HN"},{"name": "Hong Kong", "code": "HK"},{"name": "Hungary", "code": "HU"},{"name": "Iceland", "code": "IS"},{"name": "India", "code": "IN"},{"name": "Indonesia", "code": "ID"},{"name": "Iran, Islamic Republic Of", "code": "IR"},{"name": "Iraq", "code": "IQ"},{"name": "Ireland", "code": "IE"},{"name": "Isle of Man", "code": "IM"},{"name": "Israel", "code": "IL"},{"name": "Italy", "code": "IT"},{"name": "Jamaica", "code": "JM"},{"name": "Japan", "code": "JP"},{"name": "Jersey", "code": "JE"},{"name": "Jordan", "code": "JO"},{"name": "Kazakhstan", "code": "KZ"},{"name": "Kenya", "code": "KE"},{"name": "Kiribati", "code": "KI"},{"name": "Korea, Democratic People"S Republic of", "code": "KP"},{"name": "Korea, Republic of", "code": "KR"},{"name": "Kuwait", "code": "KW"},{"name": "Kyrgyzstan", "code": "KG"},{"name": "Lao People"S Democratic Republic", "code": "LA"},{"name": "Latvia", "code": "LV"},{"name": "Lebanon", "code": "LB"},{"name": "Lesotho", "code": "LS"},{"name": "Liberia", "code": "LR"},{"name": "Libyan Arab Jamahiriya", "code": "LY"},{"name": "Liechtenstein", "code": "LI"},{"name": "Lithuania", "code": "LT"},{"name": "Luxembourg", "code": "LU"},{"name": "Macao", "code": "MO"},{"name": "Macedonia, The Former Yugoslav Republic of", "code": "MK"},{"name": "Madagascar", "code": "MG"},{"name": "Malawi", "code": "MW"},{"name": "Malaysia", "code": "MY"},{"name": "Maldives", "code": "MV"},{"name": "Mali", "code": "ML"},{"name": "Malta", "code": "MT"},{"name": "Marshall Islands", "code": "MH"},{"name": "Martinique", "code": "MQ"},{"name": "Mauritania", "code": "MR"},{"name": "Mauritius", "code": "MU"},{"name": "Mayotte", "code": "YT"},{"name": "Mexico", "code": "MX"},{"name": "Micronesia, Federated States of", "code": "FM"},{"name": "Moldova, Republic of", "code": "MD"},{"name": "Monaco", "code": "MC"},{"name": "Mongolia", "code": "MN"},{"name": "Montserrat", "code": "MS"},{"name": "Morocco", "code": "MA"},{"name": "Mozambique", "code": "MZ"},{"name": "Myanmar", "code": "MM"},{"name": "Namibia", "code": "NA"},{"name": "Nauru", "code": "NR"},{"name": "Nepal", "code": "NP"},{"name": "Netherlands", "code": "NL"},{"name": "Netherlands Antilles", "code": "AN"},{"name": "New Caledonia", "code": "NC"},{"name": "New Zealand", "code": "NZ"},{"name": "Nicaragua", "code": "NI"},{"name": "Niger", "code": "NE"},{"name": "Nigeria", "code": "NG"},{"name": "Niue", "code": "NU"},{"name": "Norfolk Island", "code": "NF"},{"name": "Northern Mariana Islands", "code": "MP"},{"name": "Norway", "code": "NO"},{"name": "Oman", "code": "OM"},{"name": "Pakistan", "code": "PK"},{"name": "Palau", "code": "PW"},{"name": "Palestinian Territory, Occupied", "code": "PS"},{"name": "Panama", "code": "PA"},{"name": "Papua New Guinea", "code": "PG"},{"name": "Paraguay", "code": "PY"},{"name": "Peru", "code": "PE"},{"name": "Philippines", "code": "PH"},{"name": "Pitcairn", "code": "PN"},{"name": "Poland", "code": "PL"},{"name": "Portugal", "code": "PT"},{"name": "Puerto Rico", "code": "PR"},{"name": "Qatar", "code": "QA"},{"name": "Reunion", "code": "RE"},{"name": "Romania", "code": "RO"},{"name": "Russian Federation", "code": "RU"},{"name": "RWANDA", "code": "RW"},{"name": "Saint Helena", "code": "SH"},{"name": "Saint Kitts and Nevis", "code": "KN"},{"name": "Saint Lucia", "code": "LC"},{"name": "Saint Pierre and Miquelon", "code": "PM"},{"name": "Saint Vincent and the Grenadines", "code": "VC"},{"name": "Samoa", "code": "WS"},{"name": "San Marino", "code": "SM"},{"name": "Sao Tome and Principe", "code": "ST"},{"name": "Saudi Arabia", "code": "SA"},{"name": "Senegal", "code": "SN"},{"name": "Serbia and Montenegro", "code": "CS"},{"name": "Seychelles", "code": "SC"},{"name": "Sierra Leone", "code": "SL"},{"name": "Singapore", "code": "SG"},{"name": "Slovakia", "code": "SK"},{"name": "Slovenia", "code": "SI"},{"name": "Solomon Islands", "code": "SB"},{"name": "Somalia", "code": "SO"},{"name": "South Africa", "code": "ZA"},{"name": "South Georgia and the South Sandwich Islands", "code": "GS"},{"name": "Spain", "code": "ES"},{"name": "Sri Lanka", "code": "LK"},{"name": "Sudan", "code": "SD"},{"name": "Suriname", "code": "SR"},{"name": "Svalbard and Jan Mayen", "code": "SJ"},{"name": "Swaziland", "code": "SZ"},{"name": "Sweden", "code": "SE"},{"name": "Switzerland", "code": "CH"},{"name": "Syrian Arab Republic", "code": "SY"},{"name": "Taiwan, Province of China", "code": "TW"},{"name": "Tajikistan", "code": "TJ"},{"name": "Tanzania, United Republic of", "code": "TZ"},{"name": "Thailand", "code": "TH"},{"name": "Timor-Leste", "code": "TL"},{"name": "Togo", "code": "TG"},{"name": "Tokelau", "code": "TK"},{"name": "Tonga", "code": "TO"},{"name": "Trinidad and Tobago", "code": "TT"},{"name": "Tunisia", "code": "TN"},{"name": "Turkey", "code": "TR"},{"name": "Turkmenistan", "code": "TM"},{"name": "Turks and Caicos Islands", "code": "TC"},{"name": "Tuvalu", "code": "TV"},{"name": "Uganda", "code": "UG"},{"name": "Ukraine", "code": "UA"},{"name": "United Arab Emirates", "code": "AE"},{"name": "United Kingdom", "code": "GB"},{"name": "United States", "code": "US"},{"name": "United States Minor Outlying Islands", "code": "UM"},{"name": "Uruguay", "code": "UY"},{"name": "Uzbekistan", "code": "UZ"},{"name": "Vanuatu", "code": "VU"},{"name": "Venezuela", "code": "VE"},{"name": "Viet Nam", "code": "VN"},{"name": "Virgin Islands, British", "code": "VG"},{"name": "Virgin Islands, U.S.", "code": "VI"},{"name": "Wallis and Futuna", "code": "WF"},{"name": "Western Sahara", "code": "EH"},{"name": "Yemen", "code": "YE"},{"name": "Zambia", "code": "ZM"},{"name": "Zimbabwe", "code": "ZW"}]'

DECLARE @TestTable TABLE (  
    Id INT PRIMARY KEY IDENTITY,  
    CompressedData VARBINARY(max),
    UnCompressedData VARCHAR(MAX),
    JSONData AS CAST(decompress(CompressedData) AS VARCHAR(MAX))  
);

INSERT INTO @TestTable (CompressedData, UnCompressedData)
VALUES (COMPRESS(@jsontext), @jsontext)

SELECT UnCompressedData, DATALENGTH(CompressedData) CompressedSize
FROM @TestTable

SELECT CAST(DECOMPRESS(CompressedData) AS VARCHAR(MAX)) AS Decompressed, DATALENGTH(UnCompressedData) UncompressedSize
FROM @TestTable

cmd.CommandText = @"INSERT INTO [dbo].[Patients] 
([SSN], [FirstName], [LastName], [BirthDate]) 
VALUES (@SSN, @FirstName, @LastName, @BirthDate);";
 
 SqlParameter paramSSN = cmd.CreateParameter();
 paramSSN.ParameterName = @"@SSN";
 paramSSN.DbType = DbType.String;
 paramSSN.Direction = ParameterDirection.Input;
 paramSSN.Value = ssn;
 paramSSN.Size = 11;
 cmd.Parameters.Add(paramSSN);
 …
 SqlParameter paramBirthdate = cmd.CreateParameter();
 paramBirthdate.ParameterName = @"@BirthDate";
 paramBirthdate.DbType = DbType.DateTime2;
 paramBirthdate.Direction = ParameterDirection.Input;
 paramBirthdate.Value = birthdate;
 cmd.Parameters.Add(paramBirthdate);
 
 cmd.ExecuteNonQuery();

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
|             SSN |        FirstName |         LastName |                 BirthDate |
-------------------------------------------------------------------------------------
|     123-45-6789 |             John |              Doe |      Friday, May 21, 1971 |
-------------------------------------------------------------------------------------
|     111-22-3333 |           Joanne |              Doe | Sunday, December 01, 1974 |
-------------------------------------------------------------------------------------
|     562-00-6354 |          Michael |             Park | Sunday, November 18, 1928 |
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

ALTER Table [dbo].[Employees] ALTER COLUMN [Salary] 
ADD MASKED WITH (FUNCTION='default()')
GO
ALTER Table [dbo].[Employees] ALTER COLUMN [CreditCard] 
ADD MASKED WITH (FUNCTION='partial(2,"XXXX",2)')
GO
ALTER Table [dbo].[Employees] ALTER COLUMN [EmailAddress] 
ADD MASKED WITH (FUNCTION='email()')
GO
ALTER Table [dbo].[Employees] ALTER COLUMN [PhoneNumber] 
ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)')
GO

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'testuser', DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Demo16]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestUser]
GO

EXECUTE AS USER='TestUser';
SELECT * FROM dbo.Employees;
REVERT;

-- Removing the UNMASK permission
REVOKE UNMASK TO TestUser;

EXECUTE AS USER='TestUser';
SELECT * FROM dbo.Employees;
REVERT;

--Showing First and Second Insert
SELECT * from dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:33:00.0004984'

--Showing after all three inserts
SELECT * from dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:00.0004984'

--As Of for Group 2 - Showing Before/After updates/deletes
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:23.3944162' 
WHERE [UserGroupId] = 2
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2015-06-19 20:34:25.3944162' 
WHERE [UserGroupId] = 2

--Current State
SELECT * FROM dbo.MyTemporalTable

INSERT INTO dbo.Addresses
	(UserId,AddressLine1,AddressLine2,AddressLine3,City,State,ZipCode)
VALUES
	(1, '123 Main Street', NULL, NULL, 'Jacksonville',  'FL', '32225' ),
	(2, '5th Ave South', 'Suite 500', NULL, 'New York', 'NY','01032'),
	(3, 'Attn: Returns Department', '1 Google Way','Building 25',  'San Fancisco', 'CA','66435');

SELECT
    CONCAT_WS(', ', AddressLine1, AddressLine2, AddressLine3, City, [State], ZipCode) AS MailingAddress
FROM
    dbo.Addresses;

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

SELECT 
    TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point, 
    TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;

use tempdb
go
SELECT object_name(page_info.object_id), page_info.* 
FROM sys.dm_exec_requests AS d 
  CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r
  CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id,'DETAILED')
    AS page_info
GO