DDL n n n n ALTER Collations CREATE

DDL n n n n ALTER Collations CREATE DROP DISABLE TRIGGER ENABLE TRIGGER RENAME (? ? ? ) UPDATE STATISTICS https: //docs. microsoft. com/en-us/sql/t-sql/statements? view=sql-server-2017

CREATE / ALTER / DROP n n n CREATE CREATE DATABASE SCHEMA TABLE VIEW TRIGGER n n n CREATE AGGREGATE CREATE ASSEMBLY CREATE TYPE n CREATE SEQUENCE n n n CREATE FULLTEXT CATALOG FULLTEXT STOPLIST SEARCH PROPERTY LIST FULLTEXT INDEX Подчеркнутые не поддерживают ALTER https: //msdn. microsoft. com/ru-ru/library/cc 879262(v=sql. 120). aspx n n n CREATE INDEX COLUMNSTORE INDEX SPATIAL INDEX STATISTICS CREATE PROCEDURE FUNCTION PARTITION SCHEME CREATE LOGIN CREATE ROLE CREATE USER CREATE XML INDEX CREATE XML SCHEMA COLLECTION

DROP [ IF EXISTS ] -- Syntax for SQL Server and Azure SQL Database DROP TABLE [ IF EXISTS ] [ database_name. [ schema_name ]. | schema_name. ] table_name [ , . . . n ] [ ; ] DROP STATISTICS table. statistics_name | view. statistics_name [ , . . . n ]

Collations SELECT name, description FROM fn_helpcollations();

Collations CREATE TABLE Locations (Place varchar(15) NOT NULL); GO INSERT Locations(Place) VALUES ('Chiapas'), ('Colima') , ('Cinco Rios'), ('California'); GO --Apply an typical collation SELECT Place FROM Locations ORDER BY Place COLLATE Latin 1_General_CS_AS_KS_WS ASC; GO -- Apply a Spanish collation SELECT Place FROM Locations ORDER BY Place COLLATE Traditional_Spanish_ci_ai ASC; GO https: //docs. microsoft. com/en-us/sql/t-sql/statements/collations? view=sql-server-2017

Collations CREATE TABLE Test. Tab ( id int, Greek. Col nvarchar(10) collate greek_ci_as, Latin. Col nvarchar(10) collate latin 1_general_cs_as ) INSERT Test. Tab VALUES (1, N'A', N'a'); GO SELECT * FROM Test. Tab WHERE Greek. Col = Latin. Col; Msg 448, Level 16, State 9, Line 2 Cannot resolve collation conflict between 'Latin 1_General_CS_AS' and 'Greek_CI_AS' in equal to operation. https: //docs. microsoft. com/en-us/sql/t-sql/statements/collations? view=sql-server-2017

ENABLE/DISABLE TRIGGER { [ schema_name. ] trigger_name [ , . . . n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] ENABLE TRIGGER { [ schema_name. ] trigger_name [ , . . . n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] enable trigger [After. Update. Student] on [dbo]. [Student] alter table [dbo]. [Student] enable trigger [After. Update. Student]

RENAME (? ? ? ) Table Name exec sp_rename 'db_name. old_table_name', 'new_table_name' Column exec sp_rename 'db_name. old_table_name', 'user. Name', 'COLUMN' Index exec sp_rename 'db_name. old_table_name. id', 'product_ID', 'INDEX' --also available for statics and datatypes

UPDATE STATISTICS -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name } | ( { index_or_statistics_name } [ , . . . n ] ) } ] [ WITH [ FULLSCAN [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ] | SAMPLE number { PERCENT | ROWS } [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ] | RESAMPLE [ ON PARTITIONS ( { <partition_number> | <range> } [, …n] ) ] | <update_stats_stream_option> [ , . . . n ] ] [ [ , ] [ ALL | COLUMNS | INDEX ] [ [ , ] NORECOMPUTE ] [ [ , ] INCREMENTAL = { ON | OFF } ] [ [ , ] MAXDOP = max_degree_of_parallelism ] ]; <update_stats_stream_option> : : = [ STATS_STREAM = stats_stream ] [ ROWCOUNT = numeric_constant ] [ PAGECOUNT = numeric_contant ] USE Adventure. Works 2012; GO UPDATE STATISTICS Sales. Order. Detail; GO USE Adventure. Works 2012; GO UPDATE STATISTICS Production. Product(Products) WITH FULLSCAN, NORECOMPUTE; GO https: //docs. microsoft. com/en-us/sql/t-sql/statements/update-statistics-transact-sql? view=sql-server-2017

DML n n n BULK INSERT DELETE INSERT UPDATE MERGE TRUNCATE TABLE https: //docs. microsoft. com/en-us/sql/t-sql/statements? view=sql-server-2017

BULK INSERT

DELETE Sales. Person. Quota. History FROM Sales. Person. Quota. History AS spqh INNER JOIN Sales. Person AS sp ON spqh. Business. Entity. ID = sp. Business. Entity. ID WHERE sp. Sales. YTD > 2500000. 00;

INSERT n TOP – без сортировки! https: //docs. microsoft. com/ru-ru/sql/t-sql/statements/insert-transact-sql? view=sql-server-2017

INSERT INTO Production. Unit. Measure ( Name, Unit. Measure. Code, Modified. Date ) VALUES ( 'Square Yards', 'Y 2', GETDATE() );

UPDATE n TOP – без сортировки! https: //docs. microsoft. com/ru-ru/sql/t-sql/ queries/update-transact-sql? view=sql-server-2017

UPDATE Production. Product SET Color = N'Metallic Red' WHERE [Name] LIKE 'Road-250%' AND [Color] = 'Red';

Когда TOP+UNION – зло! CREATE TABLE dbo. Cars(Model varchar(15), Price money, Color varchar(10)); INSERT dbo. Cars VALUES ('sedan', 10000, 'red'), ('convertible', 15000, 'blue'), ('coupe', 20000, 'red'), ('van', 8000, 'blue'); https: //docs. microsoft. com/ru-ru/sql/t-sql/queries/top-transact-sql? view=sql-server-2017

Подсказки (I, U, D, S, M) n Для запроса https: //docs. microsoft. com/ru-ru/sql/t-sql/queries/hints-transact-sql-query? view=sql-server-2017

Подсказки (I, U, D, S, M) n Для таблиц https: //docs. microsoft. com/ru-ru/sql/t-sql/queries/hints-transact-sql-query? view=sql-server-2017

Подсказки (I, U, D, S, M) n Для соединения таблиц SELECT p. Name, pr. Product. Review. ID FROM Production. Product AS p LEFT OUTER HASH JOIN Production. Product. Review AS pr ON p. Product. ID = pr. Product. ID ORDER BY Product. Review. ID DESC DELETE FROM Sales. Person. Quota. History AS spqh INNER LOOP JOIN Sales. Person AS sp ON spqh. Sales. Person. ID = sp. Sales. Person. ID WHERE sp. Sales. YTD > 2500000. 00 SELECT poh. Purchase. Order. ID, poh. Order. Date, pod. Product. ID, pod. Due. Date, poh. Vendor. ID FROM Purchasing. Purchase. Order. Header AS ph INNER MERGE JOIN Purchasing. Purchase. Order. Detail AS pd ON ph. Purchase. Order. ID = pd. Purchase. Order. ID https: //docs. microsoft. com/ru-ru/sql/t-sql/queries/hints-transact-sql-join? view=sql-server-2017