1. parse

Returns the result of an expression, translated to the requested data type in SQL Server
Uses CULTURE parameter

PARSE ( string_value AS data_type [ USING culture ] )  

2.format

FORMAT ( value, format [, culture ] )  

3. choose

Choose a value from a list based on ID value
CHOOSE(Index, Val 1, Val 2, …[, Val n])

4.iif

• Specify both true and false results of a Boolean expression in single line
• IIF(Condition, True Val, False Val)
  如果条件是真的，return true value

5.Sequence Objects

• like create identity in multiple table, and it won't be dropped

DROP SEQUENCE DecSeq
GO

CREATE SEQUENCE DecSeq
    AS INT 
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 10
    CYCLE
    CACHE 50

SELECT NEXT VALUE FOR DecSeq;
GO
;

SELECT *--cache_size, current_value 
FROM sys.sequences
WHERE name = 'DecSeq' ;
GO
   
CREATE TABLE #Tab_A(AID INT, AName VARCHAR(10))
GO
CREATE TABLE #Tab_B(BID INT, BName VARCHAR(10))
GO
TRUNCATE TABLE #Tab_A
GO
TRUNCATE TABLE #Tab_B
GO

INSERT INTO #Tab_A VALUES 
(NEXT VALUE FOR DecSeq,'A')
GO
INSERT INTO #Tab_B VALUES 
(NEXT VALUE FOR DecSeq,'B')
GO
    
SELECT * FROM #Tab_A
SELECT * FROM #Tab_B

6.Pagination

• Allows developer to display large amounts of data, broken up into smaller data sets
• Specify OFFSET value to define where to begin your data to be displayed or how many rows you want to skip
• Use FETCH to retrieve number of rows to be featured from OFFSET point
• Must be used with ORDER BY clause
• OFFSET is mandatory FETCH is not
• When FETCH is not defined all the data from OFFSET point is displayed

可以找2nd,3rd工资最高的人

7. contained database

• Contained databases fix a common headache involved in migration, or moving a database to a new server

• Common metadata, security, and users accounts associated with any database is stored in Master system database but with contained database this info is stored on the same user database

• SQL Server 2012 supports Partial Contained database

• Contained database property can be changed in database properties

• User can connect to a contained database without a login by creating a user in the contained database as “SQL user with password”

• partial

SS 16 feature

8. drop table

drop table if exists dbo.employee
go

9. proc,func,trigger,view

create or alter proc P_name
as
select * from sales

10. string_split

• table function

11. compress and decompress

• pay attention small values, it might biger than original one

• for some data we nare not often use, compress all column to xml and then compress

• decompress need **cast **function as varchar

• decompress can not use by itself,need conversion function

• decompress can use with like(inplicit conversion function)

12. string_agg

convert column to row and store as string

13. concact_ws

14. datediff_big()

• use in getting big number

15. compare execution plan (2012)

right click---->compare show plan

16. temporal tables

记录table的修改时间

• 可以记录删除，更新的记录
• history table

starttime,endtime, period for system_

• if you want drop, before you drop the table, use set (system_versioning = off) first

17. always encrypted

18.dynamic data masking

• can control which user can see
• revoke() unmask
• can not use in view
  -ALTER COLUMN CNAME DROP MASKED
• default;email;phone;customer

19.query store

• store

20. updatable non clustered column store index

21.opitimazation

• it is a iterative process,time-consuming process

• think like -------DEV: use what you have,changing code
  --------DBA: think index,partition, disk type,drive

• technique:

22. hint

select * from sales
select * from sales with (nolock)
when you reading, someone can multiple data

when i a new dev, i am a new one, i want to learn the data. if i select * from sales, other can not modify, but if with no locks, other people can mpdify. but it will cause question: wrong data. only way is waiting...

23. you can truncate partition