Ducks, Thailand, and T-SQL ... or what might lurk programmers when working with SQL Server?
It all started quite mundanely ... Richter read out and strenuously studied Schild. I thought that I would be engaged in developing for .NET , but fate in the first month of work decided otherwise. One of the employees unexpectedly left the project and threw fresh human material into the newly formed hole. That's when my acquaintance with SQL Server began .
A little less than 6 years have passed since then and much can be remembered ...
About a former client of Joseph from England, who rethought life, during a vacation in Thailand, and Josephine began to sign on my Skype. About cheerful office neighbors who had to sit in the same room: one suffered from allergies to fresh air, and the other toiled with unrequited love for C ++, complementing this with allergies to sunlight. Once upon a command from above, it was necessary for the time being to become Alexander the father of two children, in order to pretend to be a senior senior with JS .
But the most fierce trash is probably connected with the story of a rubber duck-squeaker. One colleague took off her stress and, once, in a fit of emotion, bit her head off. Since then, the duck has lost its former gloss and was soon replaced with a ball, which he sometimes tried to gnaw ... alas, without success.
')
Why was this told? If you want to devote your life to working with databases, then the first thing you need to learn ... is stress resistance. The second is to adopt a few rules when writing queries for T-SQL , which many of the novice developers do not know or simply ignore, and then sit and puzzle ... why is something not working?
Content:
1. Data Types
2. *
3. Alias
4. Column order
5. NOT IN vs NULL
6. Date format
7. Date filter
8. Calculation
9. Convert implicit
10. LIKE & Suppressed index
11. Unicode vs ANSI
12. COLLATE
13. BINARY COLLATE
14. Code style
15. [var] char
16. Data length
17. ISNULL vs COALESCE
18. Math
19. UNION vs UNION ALL
20. Re-read
21. SubQuery
22. CASE WHEN
23. Scalar func
24. VIEWs
25. CURSORs
26. STRING_CONCAT
27. SQL Injection
Video with examples
1. Data Types
The most basic thing with which most problems begin when working with SQL Server is the wrong choice of data types. Take a hypothetical example with two identical tables in essence:
DECLARE @Employees1 TABLE ( EmployeeID BIGINT PRIMARY KEY , IsMale VARCHAR(3) , BirthDate VARCHAR(20) ) INSERT INTO @Employees1 VALUES (123, 'YES', '2012-09-01') DECLARE @Employees2 TABLE ( EmployeeID INT PRIMARY KEY , IsMale BIT , BirthDate DATE ) INSERT INTO @Employees2 VALUES (123, 1, '2012-09-01') Perform a query and see what the difference is:
DECLARE @BirthDate DATE = '2012-09-01' SELECT * FROM @Employees1 WHERE BirthDate = @BirthDate SELECT * FROM @Employees2 WHERE BirthDate = @BirthDate 
In the first case, the data types are more redundant than they could be. Why store the bit sign as a string YES / NO ? Why keep the date as a string? Why BIGINT according to the table with employees? Than simple int did not fit?
This is bad for several reasons: the tables will take up more disk space, you need to read more pages from the disk and place more pages in the BufferPool to handle this data. In addition, there may be more serious performance problems - a question mark easily hints at this, but we'll talk about this later.
2. *
Often we had to meet the “oil painting”: all data is taken from the table, and then on the client through the DataReader only those columns that are really needed are selected. It is extremely inefficient, so it is better not to use this practice:
USE AdventureWorks2014 GO SET STATISTICS TIME, IO ON SELECT * FROM Person.Person SELECT BusinessEntityID , FirstName , MiddleName , LastName FROM Person.Person SET STATISTICS TIME, IO OFF The difference will be both in the time the query is executed and in the fact that it will be possible to do less logical readings due to the covering index:
Table 'Person'. Scan count 1, logical reads 3819, physical reads 3, ... SQL Server Execution Times: CPU time = 31 ms, elapsed time = 1235 ms. Table 'Person'. Scan count 1, logical reads 109, physical reads 1, ... SQL Server Execution Times: CPU time = 0 ms, elapsed time = 227 ms. 3. Alias
Create a table:
USE AdventureWorks2014 GO IF OBJECT_ID('Sales.UserCurrency') IS NOT NULL DROP TABLE Sales.UserCurrency GO CREATE TABLE Sales.UserCurrency ( CurrencyCode NCHAR(3) PRIMARY KEY ) INSERT INTO Sales.UserCurrency VALUES ('USD') Suppose we have a query that returns the number of identical rows in both tables:
SELECT COUNT_BIG(*) FROM Sales.Currency WHERE CurrencyCode IN ( SELECT CurrencyCode FROM Sales.UserCurrency ) And everything will work, as we expect, until someone wants to rename a column in the Sales.UserCurrency table:
EXEC sys.sp_rename 'Sales.UserCurrency.CurrencyCode', 'Code', 'COLUMN' Let's execute the query and see that it is not 1 row that is returned, but all that are in Sales.Currency . When building a SQL Server execution plan at the binding stage, it will look at the columns. Sales.UserCurrency will not find the CurrencyCode there and will think that this column refers to the Sales.Currency table and then the optimizer will omit the condition CurrencyCode = CurrencyCode .
Moral - use aliases:
SELECT COUNT_BIG(*) FROM Sales.Currency c WHERE c.CurrencyCode IN ( SELECT u.CurrencyCode FROM Sales.UserCurrency u ) 4. Column order
Suppose we have some kind of table:
IF OBJECT_ID('dbo.DatePeriod') IS NOT NULL DROP TABLE dbo.DatePeriod GO CREATE TABLE dbo.DatePeriod ( StartDate DATE , EndDate DATE ) And we always insert the data into it from the assumption that we know how the columns are arranged in order:
INSERT INTO dbo.DatePeriod SELECT '2015-01-01', '2015-01-31' Then one fine day, someone reverses the order of the columns:
CREATE TABLE dbo.DatePeriod ( EndDate DATE , StartDate DATE ) And the data will already be inserted in the wrong columns in which the developer expects. Therefore, it is always recommended to explicitly specify columns in the INSERT construct:
INSERT INTO dbo.DatePeriod (StartDate, EndDate) SELECT '2015-01-01', '2015-01-31' There is another interesting example:
SELECT TOP(1) * FROM dbo.DatePeriod ORDER BY 2 DESC Which column will be sorted? And it all depends on the current order in the table. If someone changes it, then the request will not display what we expect.
5. NOT IN vs NULL
The undisputed leader among the questions at the interview Junior DB Developer - construction NOT IN .
For example, you need to write a couple of queries: return all records from the first table that are not in the second and vice versa. Very often, novice developers do not bother and use IN and NOT IN :
DECLARE @t1 TABLE (t1 INT, UNIQUE CLUSTERED(t1)) INSERT INTO @t1 VALUES (1), (2) DECLARE @t2 TABLE (t2 INT, UNIQUE CLUSTERED(t2)) INSERT INTO @t2 VALUES (1) SELECT * FROM @t1 WHERE t1 NOT IN (SELECT t2 FROM @t2) SELECT * FROM @t1 WHERE t1 IN (SELECT t2 FROM @t2) The first request returned us a deuce, the second - one. Let's now add another value to the second table — NULL :
INSERT INTO @t2 VALUES (1), (NULL) When executing a query with NOT IN, we will not get any results. Has any magic intervened - IN works, and NOT IN refuses. This is the first thing that you need to “understand and forgive” when working with SQL Server , which is guided by tertiary logic during a comparison operation: TRUE , FALSE , UNKNOWN .
When running, SQL Server interprets the IN clause:
a IN (1, NULL) == a=1 OR a=NULL NOT IN :
a NOT IN (1, NULL) == a<>1 AND a<>NULL Comparing any value with NULL returns UNKNOWN . 1 = NULL , NULL = NULL . The result will be one - UNKNOWN . And since we use the AND operator in the condition, the whole expression will return an undefined value and, as a result, will be empty.
Written a bit boring. But it is important to understand that this situation occurs quite often. For example, before a column was declared NOT NULL , then some kind person allowed to write a NULL value to it. The result: the client stops working report after at least one NULL value gets into the table.
What to do? You can explicitly discard NULL values:
SELECT * FROM @t1 WHERE t1 NOT IN ( SELECT t2 FROM @t2 WHERE t2 IS NOT NULL ) You can use EXCEPT :
SELECT * FROM @t1 EXCEPT SELECT * FROM @t2 If there is no desire to think a lot, then it is easier to use NOT EXISTS :
SELECT * FROM @t1 WHERE NOT EXISTS( SELECT 1 FROM @t2 WHERE t1 = t2 ) Which query option is more optimal? Preferred is the last option with NOT EXISTS , which generates a more optimal predicate pushdown statement when accessing data from the second table.
In general, with NULL values, many jokes. You can play around with such queries:
USE AdventureWorks2014 GO SELECT COUNT_BIG(*) FROM Production.Product SELECT COUNT_BIG(*) FROM Production.Product WHERE Color = 'Grey' SELECT COUNT_BIG(*) FROM Production.Product WHERE Color <> 'Grey' and not to get the expected result just because separate comparison operators are provided for NULL values:
SELECT COUNT_BIG(*) FROM Production.Product WHERE Color IS NULL SELECT COUNT_BIG(*) FROM Production.Product WHERE Color IS NOT NULL Even more interesting is the situation with CHECK constraints:
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp GO CREATE TABLE #temp ( Color VARCHAR(15) --NULL , CONSTRAINT CK CHECK (Color IN ('Black', 'White')) ) We create a table in which we allow recording only white and black colors:
INSERT INTO #temp VALUES ('Black') (1 row(s) affected) Everything works as we expect:
INSERT INTO #temp VALUES ('Red') The INSERT statement conflicted with the CHECK constraint... The statement has been terminated. But let's insert null :
INSERT INTO #temp VALUES (NULL) (1 row(s) affected) Our CHECK constraint did not work, because NOT FALSE is enough for writing, i.e. and TRUE and UNKNOWN are suitable for a sweet soul. There are several options to bypass this behavior: explicitly declare a column as NOT NULL or consider NULL in the constraint.
6. Date format
More often stumble on different nuances with data types. For example, you need to get the current time. Executed the GETDATE function:
SELECT GETDATE() Copied the result, pasted it into the query as it is and removed the time:
SELECT * FROM sys.objects WHERE create_date < '2016-11-14' Is it correct to do so?
The date is set to a string constant, and to some extent, SQL Server allows freedom when writing it:
SET LANGUAGE English SET DATEFORMAT DMY DECLARE @d1 DATETIME = '05/12/2016' , @d2 DATETIME = '2016/12/05' , @d3 DATETIME = '2016-12-05' , @d4 DATETIME = '05-dec-2016' SELECT @d1, @d2, @d3, @d4 All values ​​are almost everywhere interpreted uniquely:
----------- ----------- ----------- ----------- 2016-12-05 2016-05-12 2016-05-12 2016-12-05 And this will not lead to problems until the request with such business logic is started on another server, on which the settings may differ:
SET DATEFORMAT MDY DECLARE @d1 DATETIME = '05/12/2016' , @d2 DATETIME = '2016/12/05' , @d3 DATETIME = '2016-12-05' , @d4 DATETIME = '05-dec-2016' SELECT @d1, @d2, @d3, @d4 All these options can lead to misinterpretation of the date:
----------- ----------- ----------- ----------- 2016-05-12 2016-12-05 2016-12-05 2016-12-05 Moreover, such code can lead to an error both explicitly and hidden. For example, we need to insert data into a table. On the test server, everything works fine:
DECLARE @t TABLE (a DATETIME) INSERT INTO @t VALUES ('05/13/2016') And the client, due to the difference in server settings, such a request will lead to problems:
DECLARE @t TABLE (a DATETIME) SET DATEFORMAT DMY INSERT INTO @t VALUES ('05/13/2016') Msg 242, Level 16, State 3, Line 28 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. So in what format to set constants for dates? Let's look at another example:
SET DATEFORMAT YMD SET LANGUAGE English DECLARE @d1 DATETIME = '2016/01/12' , @d2 DATETIME = '2016-01-12' , @d3 DATETIME = '12-jan-2016' , @d4 DATETIME = '20160112' SELECT @d1, @d2, @d3, @d4 GO SET LANGUAGE Deutsch DECLARE @d1 DATETIME = '2016/01/12' , @d2 DATETIME = '2016-01-12' , @d3 DATETIME = '12-jan-2016' , @d4 DATETIME = '20160112' SELECT @d1, @d2, @d3, @d4 Depending on the language set, constants can also be interpreted differently:
----------- ----------- ----------- ----------- 2016-01-12 2016-01-12 2016-01-12 2016-01-12 ----------- ----------- ----------- ----------- 2016-12-01 2016-12-01 2016-01-12 2016-01-12 And the conclusion suggests the use of the last two options. At once I will say that explicitly setting the month is a good opportunity to stumble upon the “not a manj pa sys jour” error:
SET LANGUAGE French DECLARE @d DATETIME = '12-jan-2016' Msg 241, Level 16, State 1, Line 29 Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères. Total - remains the last option. If you want constants with dates to be unambiguously interpreted in the system, regardless of the settings and phase of the moon, then specify them in the format YYYYMMDD without any tildes, quotes and slashes.
Another thing to note is the difference in the behavior of some data types:
SET LANGUAGE English SET DATEFORMAT YMD DECLARE @d1 DATE = '2016-01-12' , @d2 DATETIME = '2016-01-12' SELECT @d1, @d2 GO SET LANGUAGE Deutsch SET DATEFORMAT DMY DECLARE @d1 DATE = '2016-01-12' , @d2 DATETIME = '2016-01-12' SELECT @d1, @d2 Unlike DATETIME , the DATE type is correctly interpreted with different settings on the server:
---------- ---------- 2016-01-12 2016-01-12 ---------- ---------- 2016-01-12 2016-12-01 But do you need to keep this nuance in your head? Hardly. The main thing to remember is that you need to set the dates in the format YYYYMMDD and there will be no problems.
7. Date filter
Next, consider how to effectively filter data. For some reason, the DATETIME / DATE columns have the largest number of crutches, so we will start with this data type:
USE AdventureWorks2014 GO UPDATE TOP(1) dbo.DatabaseLog SET PostTime = '20140716 12:12:12' Now let's try to find out how many rows the query will return for a certain day:
SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE PostTime = '20140716' Request returns 0. Why? When building a plan, SQL Server tries to convert a string constant to the data type of the column that is filtered:
Create an index:
CREATE NONCLUSTERED INDEX IX_PostTime ON dbo.DatabaseLog (PostTime) There are correct and incorrect options to display the required data. For example, trim time:
SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE CONVERT(CHAR(8), PostTime, 112) = '20140716' SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE CAST(PostTime AS DATE) = '20140716' Or set the range:
SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE PostTime BETWEEN '20140716' AND '20140716 23:59:59.997' SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE PostTime >= '20140716' AND PostTime < '20140717' The last two queries are more correct from the point of view of optimization. And the fact is that all conversions and calculations on the index columns being searched can dramatically reduce performance and increase logical readings (the first and last three variants of the query):
Table 'DatabaseLog'. Scan count 1, logical reads 7, ... Table 'DatabaseLog'. Scan count 1, logical reads 2, ... The PostTime field was not previously included in the index, and we would not be able to see any special effect from using the “correct” approach for filtering. Another thing, when we need to display data for the month. What just did not see:
SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE CONVERT(CHAR(8), PostTime, 112) LIKE '201407%' SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE DATEPART(YEAR, PostTime) = 2014 AND DATEPART(MONTH, PostTime) = 7 SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE YEAR(PostTime) = 2014 AND MONTH(PostTime) = 7 SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE EOMONTH(PostTime) = '20140731' SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE PostTime >= '20140701' AND PostTime < '20140801' And again, the last option is more acceptable than all the others:
In addition, you can always make a calculated field and create an index based on it:
IF COL_LENGTH('dbo.DatabaseLog', 'MonthLastDay') IS NOT NULL ALTER TABLE dbo.DatabaseLog DROP COLUMN MonthLastDay GO ALTER TABLE dbo.DatabaseLog ADD MonthLastDay AS EOMONTH(PostTime) --PERSISTED GO CREATE INDEX IX_MonthLastDay ON dbo.DatabaseLog (MonthLastDay) In comparison with the previous query, the difference in logical readings will be significant (if we are talking about large tables):
SET STATISTICS IO ON SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE PostTime >= '20140701' AND PostTime < '20140801' SELECT COUNT_BIG(*) FROM dbo.DatabaseLog WHERE MonthLastDay = '20140731' SET STATISTICS IO OFF Table 'DatabaseLog'. Scan count 1, logical reads 7, ... Table 'DatabaseLog'. Scan count 1, logical reads 3, ... 8. Calculation
As I already said, any calculations on the index fields degrade performance and lead to an increase in logical reads:
USE AdventureWorks2014 GO SET STATISTICS IO ON SELECT BusinessEntityID FROM Person.Person WHERE BusinessEntityID * 2 = 10000 SELECT BusinessEntityID FROM Person.Person WHERE BusinessEntityID = 2500 * 2 SELECT BusinessEntityID FROM Person.Person WHERE BusinessEntityID = 5000 Table 'Person'. Scan count 1, logical reads 67, ... Table 'Person'. Scan count 0, logical reads 3, ... If you look at the execution plans, in the first case SQL Server has to run IndexScan :
In the second and third cases, when the calculations are on the index field, no, we will see IndexSeek :
9. Convert implicit
First, let's look at these two queries, which are filtered by the same value:
USE AdventureWorks2014 GO SELECT BusinessEntityID, NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber = 30845 SELECT BusinessEntityID, NationalIDNumber FROM HumanResources.Employee WHERE NationalIDNumber = '30845' If you look at the execution plans:
In the first case - a warning and IndexScan , in the second - IndexSeek :
Table 'Employee'. Scan count 1, logical reads 4, ... Table 'Employee'. Scan count 0, logical reads 2, ... What happened? The NationalIDNumber column has the data type NVARCHAR (15) . The constant, by the value of which you need to filter the data, we pass as INT and as a result we get an implicit type conversion, which can degrade performance. This happens very often when someone changes the data type on a column, but the queries remain the same.
However, it is important to understand that not only performance problems can wait for us. Implicit type conversion can lead to runtime errors. For example, earlier the PostalCode field was numeric, then an indication came from above that the postal code may contain letters. The data type was changed, but as soon as the letter postal code is inserted, the old query will no longer work:
SELECT AddressID FROM Person.[Address] WHERE PostalCode = 92700 SELECT AddressID FROM Person.[Address] WHERE PostalCode = '92700' Msg 245, Level 16, State 1, Line 16 Conversion failed when converting the nvarchar value 'K4B 1S2' to data type int. Even more interesting is when the project uses EntityFramework , which interprets all string fields as Unicode by default:
SELECT CustomerID, AccountNumber FROM Sales.Customer WHERE AccountNumber = N'AW00000009' SELECT CustomerID, AccountNumber FROM Sales.Customer WHERE AccountNumber = 'AW00000009' And as a result, we have generated not quite optimal requests:
The solution to the problem is quite simple - you need to control that the data types match when comparing.
10. LIKE & Suppressed index
Even when you have a covering index, it’s not a fact that it will be used effectively. For example, we need to print all the lines that start with ...
USE AdventureWorks2014 GO SET STATISTICS IO ON SELECT AddressLine1 FROM Person.[Address] WHERE SUBSTRING(AddressLine1, 1, 3) = '100' SELECT AddressLine1 FROM Person.[Address] WHERE LEFT(AddressLine1, 3) = '100' SELECT AddressLine1 FROM Person.[Address] WHERE CAST(AddressLine1 AS CHAR(3)) = '100' SELECT AddressLine1 FROM Person.[Address] WHERE AddressLine1 LIKE '100%' We get the following logical reads:
Table 'Address'. Scan count 1, logical reads 216, ... Table 'Address'. Scan count 1, logical reads 216, ... Table 'Address'. Scan count 1, logical reads 216, ... Table 'Address'. Scan count 1, logical reads 4, ... Fulfillment plans for quickly finding a winner:
The result is the sum we talked about so long before. If there is an index, then it should not have any calculations and type conversions, functions, and other things. Only then will it be effectively used by SQL Server .
But what if you need to find all occurrences of a substring in a string? This problem is obviously more interesting:
SELECT AddressLine1 FROM Person.[Address] WHERE AddressLine1 LIKE '%100%' But first we need to learn a lot of interesting things about strings and their properties.
11. Unicode vs ANSI
The first thing to remember is that strings are UNICODE and ANSI . For the first types, data types NVARCHAR / NCHAR are provided (2 bytes per symbol - alas, UTF8 was not delivered). To store ANSI strings - VARCHAR / CHAR (1 byte - 1 character). There is also TEXT / NTEXT , but it is better to forget about them initially (because by using them you can significantly reduce performance).
And it seems that this could be completed, but no ...
If a unicode constant is specified in the query, then it is necessary to put the symbol N in front of it. To show the difference, a simple query is enough:
SELECT '文本 ANSI' , N'文本 UNICODE' ------- ------------ ?? ANSI 文本 UNICODE If you do not specify N before a constant, SQL Server will try to search for a suitable character in ANSI encoding. If not found, then substitute a question mark.
12. COLLATE
I recalled one very interesting example that people like to ask during an interview for the position of Middle / Senior DB Developer . Will the data return the next request?
DECLARE @a NCHAR(1) = '' , @b NCHAR(1) = '' SELECT @a, @b WHERE @a = @b And yes ... and no ... Then how lucky. I usually answer like that.
Why such an ambiguous answer? First, the string constants should not be N, so they will be interpreted as ANSI . Second, a lot depends on the current COLLATE , which is a set of rules for sorting and comparing string data.
USE [master] GO IF DB_ID('test') IS NOT NULL BEGIN ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE test END GO CREATE DATABASE test COLLATE Latin1_General_100_CI_AS GO USE test GO DECLARE @a NCHAR(1) = '' , @b NCHAR(1) = '' SELECT @a, @b WHERE @a = @b With this COLLATE instead of Cyrillic, we get question marks, because the symbols of the question mark are equal to each other:
---- ---- ? ? If we change COLLATE to something else:
ALTER DATABASE test COLLATE Cyrillic_General_100_CI_AS And the request will not return anything, because the Cyrillic alphabet will be correctly interpreted.
Therefore, the moral here is simple: if the string constant should take UNICODE , then do not be lazy to put N in front of it. There is also the flip side of the medal, where N is molded wherever possible, and the optimizer has to perform type conversions, which, as I said before, lead to suboptimal execution plans (this was shown above).
What else did I forget to mention about the lines? Another good question from the “let's do an interview” cycle:
DECLARE @a VARCHAR(10) = 'TEXT' , @b VARCHAR(10) = 'text' SELECT IIF(@a = @b, 'TRUE', 'FALSE') Are these lines equal? And yes ... and no ... I would answer again. If we want a one-to-one comparison, we need to explicitly specify COLLATE
DECLARE @a VARCHAR(10) = 'TEXT' , @b VARCHAR(10) = 'text' SELECT IIF(@a COLLATE Latin1_General_CS_AS = @b COLLATE Latin1_General_CS_AS, 'TRUE', 'FALSE') Because COLLATE can be either case-sensitive ( CS ) or not case-sensitive ( CI ) when comparing and sorting strings. Different COLLATE at the client and on the test base is a potential source not only of logical errors in business logic.
Even more fun when the COLLATE between the target base and tempdb does not match. Create a database with COLLATE , different from the default:
USE [master] GO IF DB_ID('test') IS NOT NULL BEGIN ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE test END GO CREATE DATABASE test COLLATE Albanian_100_CS_AS GO USE test GO CREATE TABLE t (c CHAR(1)) INSERT INTO t VALUES ('a') GO IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL DROP TABLE #t1 IF OBJECT_ID('tempdb.dbo.#t2') IS NOT NULL DROP TABLE #t2 IF OBJECT_ID('tempdb.dbo.#t3') IS NOT NULL DROP TABLE #t3 GO CREATE TABLE #t1 (c CHAR(1)) INSERT INTO #t1 VALUES ('a') CREATE TABLE #t2 (c CHAR(1) COLLATE database_default) INSERT INTO #t2 VALUES ('a') SELECT c = CAST('a' AS CHAR(1)) INTO #t3 DECLARE @t TABLE (c VARCHAR(100)) INSERT INTO @t VALUES ('a') SELECT 'tempdb', DATABASEPROPERTYEX('tempdb', 'collation') UNION ALL SELECT 'test', DATABASEPROPERTYEX(DB_NAME(), 'collation') UNION ALL SELECT 't', SQL_VARIANT_PROPERTY(c, 'collation') FROM t UNION ALL SELECT '#t1', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t1 UNION ALL SELECT '#t2', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t2 UNION ALL SELECT '#t3', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t3 UNION ALL SELECT '@t', SQL_VARIANT_PROPERTY(c, 'collation') FROM @t When creating a table, COLLATE is inherited from the database. The only difference is for the first temporary table, for which we explicitly define the structure without specifying COLLATE . In this case, it inherits COLLATE from the tempdb database.
------ -------------------------- tempdb Cyrillic_General_CI_AS test Albanian_100_CS_AS t Albanian_100_CS_AS #t1 Cyrillic_General_CI_AS #t2 Albanian_100_CS_AS #t3 Albanian_100_CS_AS @t Albanian_100_CS_AS Now let's take a look at our example with # t1 , because if COLLATE does not match, this can lead to potential problems.
For example, data will not be correctly filtered due to the fact that COLLATE may be case insensitive:
SELECT * FROM #t1 WHERE c = 'A' Or SQL Server will swear on the impossibility of joining tables due to different COLLATE :
SELECT * FROM #t1 JOIN t ON [#t1].c = tc The last example is very common. On the test server, everything is perfect, and when the backup is deployed on the client's server, we get the error:
Msg 468, Level 16, State 9, Line 93 Cannot resolve the collation conflict between "Albanian_100_CS_AS" and "Cyrillic_General_CI_AS" in the equal to operation. Then you have to do crutches everywhere:
SELECT * FROM #t1 JOIN t ON [#t1].c = tc COLLATE database_default 13. BINARY COLLATE
Now, when the “fly in the ointment” is over, let's see how COLLATE can be used to advantage for itself. Remember an example about searching for a substring in a string?
SELECT AddressLine1 FROM Person.[Address] WHERE AddressLine1 LIKE '%100%' This query can be significantly optimized and shorten its execution time.
But in order to see the difference, we need to generate a large table:
USE [master] GO IF DB_ID('test') IS NOT NULL BEGIN ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE test END GO CREATE DATABASE test COLLATE Latin1_General_100_CS_AS GO ALTER DATABASE test MODIFY FILE (NAME = N'test', SIZE = 64MB) GO ALTER DATABASE test MODIFY FILE (NAME = N'test_log', SIZE = 64MB) GO USE test GO CREATE TABLE t ( ansi VARCHAR(100) NOT NULL , unicod NVARCHAR(100) NOT NULL ) GO ;WITH E1(N) AS ( SELECT * FROM ( VALUES (1),(1),(1),(1),(1), (1),(1),(1),(1),(1) ) t(N) ), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), E8(N) AS (SELECT 1 FROM E4 a, E4 b) INSERT INTO t SELECT v, v FROM ( SELECT TOP(50000) v = REPLACE(CAST(NEWID() AS VARCHAR(36)) + CAST(NEWID() AS VARCHAR(36)), '-', '') FROM E8 ) t Create computed columns with binary COLLATE , without forgetting to create indices:
ALTER TABLE t ADD ansi_bin AS UPPER(ansi) COLLATE Latin1_General_100_Bin2 ALTER TABLE t ADD unicod_bin AS UPPER(unicod) COLLATE Latin1_General_100_BIN2 CREATE NONCLUSTERED INDEX ansi ON t (ansi) CREATE NONCLUSTERED INDEX unicod ON t (unicod) CREATE NONCLUSTERED INDEX ansi_bin ON t (ansi_bin) CREATE NONCLUSTERED INDEX unicod_bin ON t (unicod_bin) Perform filtering:
SET STATISTICS TIME, IO ON SELECT COUNT_BIG(*) FROM t WHERE ansi LIKE '%AB%' SELECT COUNT_BIG(*) FROM t WHERE unicod LIKE '%AB%' SELECT COUNT_BIG(*) FROM t WHERE ansi_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2 SELECT COUNT_BIG(*) FROM t WHERE unicod_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2 SET STATISTICS TIME, IO OFF And we can see the results of implementation, which will pleasantly surprise:
SQL Server Execution Times: CPU time = 350 ms, elapsed time = 354 ms. SQL Server Execution Times: CPU time = 335 ms, elapsed time = 355 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 18 ms. SQL Server Execution Times: CPU time = 17 ms, elapsed time = 18 ms. The whole point is that the search based on a binary comparison is much faster, and if you need to frequently and quickly search for the occurrence of rows, you can store data with COLLATE , which ends in BIN . The only thing you need to remember is that all binary COLLATE case-sensitive when comparing.
14. Code style
The style of writing code is strictly individual, but in order not to bring havoc into the development, everyone has long adhered to certain rules. The most paradoxical thing is that for all the time I have not seen any imputed code of practice when writing requests. All of them are written according to the principle: “the main thing is that it should work”. Although then risk a good sip when you deploy the database on the client server.
Let's create a separate database and a table in it:
USE [master] GO IF DB_ID('test') IS NOT NULL BEGIN ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE test END GO CREATE DATABASE test COLLATE Latin1_General_CI_AS GO USE test GO CREATE TABLE dbo.Employee (EmployeeID INT PRIMARY KEY) and write this query:
select employeeid from employee Works?Now try changing COLLATE to some case-sensitive:
ALTER DATABASE test COLLATE Latin1_General_CS_AI And try to re-execute the query:
Msg 208, Level 16, State 1, Line 19 Invalid object name 'employee'. The optimizer uses the rules of the current COLLATE when building the execution plan. More precisely, at the binding stage, when a check is made for the existence of tables, columns and other objects and the comparison of each object of the syntax tree with the real object of the system catalog.
If you want to generate pens requests that will work everywhere, then you should always adhere to the correct register in the names of objects that are used in the request.
The situation with variables is even more interesting ...
For them, COLLATE is inherited from the master database . Therefore, you must observe the correct register when working with variables:
SELECT DATABASEPROPERTYEX('master', 'collation') DECLARE @EmpID INT = 1 SELECT @empid That error probably will not be:
----------------------- Cyrillic_General_CI_AS ----------- 1 At the same time on another server, an error in the register can make itself felt:
-------------------------- Latin1_General_CS_AS Msg 137, Level 15, State 2, Line 4 Must declare the scalar variable "@empid". 15. [var] char
, ( CHAR , NCHAR ) ( VARCHAR , NVARCHAR ):
DECLARE @a CHAR(20) = 'text' , @b VARCHAR(20) = 'text' SELECT LEN(@a) , LEN(@b) , DATALENGTH(@a) , DATALENGTH(@b) , '"' + @a + '"' , '"' + @b + '"' SELECT [a = b] = IIF(@a = @b, 'TRUE', 'FALSE') , [b = a] = IIF(@b = @a, 'TRUE', 'FALSE') , [a LIKE b] = IIF(@a LIKE @b, 'TRUE', 'FALSE') , [b LIKE a] = IIF(@b LIKE @a, 'TRUE', 'FALSE') 20 , 4, SQL Server 16 ( LEN DATALENGTH -):
--- --- ---- ---- ---------------------- ---------------------- 4 4 20 4 "text " "text" , — :
a = bb = aa LIKE bb LIKE a ----- ----- -------- -------- TRUE TRUE TRUE FALSE LIKE :
SELECT 1 WHERE 'a ' LIKE 'a' SELECT 1 WHERE 'a' LIKE 'a ' -- !!! SELECT 1 WHERE 'a' LIKE 'a' SELECT 1 WHERE 'a' LIKE 'a%' .
16. Data length
, :
DECLARE @a DECIMAL , @b VARCHAR(10) = '0.1' , @c SQL_VARIANT SELECT @a = @b , @c = @a SELECT @a , @c , SQL_VARIANT_PROPERTY(@c,'BaseType') , SQL_VARIANT_PROPERTY(@c,'Precision') , SQL_VARIANT_PROPERTY(@c,'Scale') ? « »:
---- ---- ---------- ----- ----- 0 0 decimal 18 0 :
DECLARE @t1 VARCHAR(MAX) = '123456789_123456789_123456789_123456789_' DECLARE @t2 VARCHAR = @t1 SELECT LEN(@t1) , @t1 , LEN(@t2) , @t2 , LEN(CONVERT(VARCHAR, @t1)) , LEN(CAST(@t1 AS VARCHAR)) , 1 :
----- ------------------------------------------ ---- ---- ---- ---- 40 123456789_123456789_123456789_123456789_ 1 1 30 30 : CAST/CONVERT , 30 .
17. ISNULL vs COALESCE
? : ISNULL COALESCE . — NULL , , COALESCE . , .
?
DECLARE @a CHAR(1) = NULL SELECT ISNULL(@a, 'NULL'), COALESCE(@a, 'NULL') DECLARE @i INT = NULL SELECT ISNULL(@i, 7.1), COALESCE(@i, 7.1) :
---- ---- N NULL ---- ---- 7 7.1 Why? ISNULL . COALESCE . , , « ».
, ISNULL , COALESCE CASE WHEN .
18. Math
, SQL Server . :
SELECT 1 / 3 SELECT 1.0 / 3 , — , . , :
----------- 0 ----------- 0.333333 , :
SELECT COUNT(*) , COUNT(1) , COUNT(val) , COUNT(DISTINCT val) , SUM(val) , SUM(DISTINCT val) FROM ( VALUES (1), (2), (2), (NULL), (NULL) ) t (val) SELECT AVG(val) , SUM(val) / COUNT(val) , AVG(val * 1.) , AVG(CAST(val AS FLOAT)) FROM ( VALUES (1), (2), (2), (NULL), (NULL) ) t (val) ? COUNT(*)/COUNT(1) . COUNT NULL . DISTINCT , , NULL .
. AVG SUM COUNT . — NULL . , , ? . .
19. UNION vs UNION ALL
: , , , , , UNION ALL . , UNION .
, UNION ALL :
SELECT [object_id] FROM sys.system_objects UNION SELECT [object_id] FROM sys.objects SELECT [object_id] FROM sys.system_objects UNION ALL SELECT [object_id] FROM sys.objects 
: UNION , UNION ALL — . , , .
, 1 , :
DECLARE @AddressLine NVARCHAR(60) SET @AddressLine = '4775 Kentucky Dr.' SELECT TOP(1) AddressID FROM Person.[Address] WHERE AddressLine1 = @AddressLine OR AddressLine2 = @AddressLine OR IndexScan :
Table 'Address'. Scan count 1, logical reads 90, ... UNION ALL :
SELECT TOP(1) AddressID FROM ( SELECT TOP(1) AddressID FROM Person.[Address] WHERE AddressLine1 = @AddressLine UNION ALL SELECT TOP(1) AddressID FROM Person.[Address] WHERE AddressLine2 = @AddressLine ) t , SQL Server , 1 , , , :
Table 'Worktable'. Scan count 0, logical reads 0, ... Table 'Address'. Scan count 1, logical reads 3, ... 20. Re-read
, JOIN :
USE AdventureWorks2014 GO SET STATISTICS IO ON SELECT e.BusinessEntityID , ( SELECT p.LastName FROM Person.Person p WHERE e.BusinessEntityID = p.BusinessEntityID ) , ( SELECT p.FirstName FROM Person.Person p WHERE e.BusinessEntityID = p.BusinessEntityID ) FROM HumanResources.Employee e SELECT e.BusinessEntityID , p.LastName , p.FirstName FROM HumanResources.Employee e JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID — :
Table 'Person'. Scan count 0, logical reads 1776, ... Table 'Employee'. Scan count 1, logical reads 2, ... Table 'Person'. Scan count 0, logical reads 888, ... Table 'Employee'. Scan count 1, logical reads 2, ... 21. SubQuery
, --.
Person.Person Sales.SalesPersonQuotaHistory , .
USE AdventureWorks2014 GO SET STATISTICS IO ON SELECT p.BusinessEntityID , ( SELECT s.SalesQuota FROM Sales.SalesPersonQuotaHistory s WHERE s.BusinessEntityID = p.BusinessEntityID ) FROM Person.Person p - :
Msg 512, Level 16, State 1, Line 6 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ?
TOP(1) ORDER BY , . , . TOP IndexSeek . OUTER/CROSS APPLY TOP :
SELECT p.BusinessEntityID , ( SELECT TOP(1) s.SalesQuota FROM Sales.SalesPersonQuotaHistory s WHERE s.BusinessEntityID = p.BusinessEntityID ORDER BY s.QuotaDate DESC ) FROM Person.Person p SELECT p.BusinessEntityID , t.SalesQuota FROM Person.Person p OUTER APPLY ( SELECT TOP(1) s.SalesQuota FROM Sales.SalesPersonQuotaHistory s WHERE s.BusinessEntityID = p.BusinessEntityID ORDER BY s.QuotaDate DESC ) t — IndexSeek :
Table 'SalesPersonQuotaHistory'. Scan count 19972, logical reads 39944, ... Table 'Person'. Scan count 1, logical reads 67, ... , :
SELECT p.BusinessEntityID , t.SalesQuota FROM Person.Person p LEFT JOIN ( SELECT s.BusinessEntityID , s.SalesQuota , RowNum = ROW_NUMBER() OVER (PARTITION BY s.BusinessEntityID ORDER BY s.QuotaDate DESC) FROM Sales.SalesPersonQuotaHistory s ) t ON p.BusinessEntityID = t.BusinessEntityID AND t.RowNum = 1 :
Table 'Person'. Scan count 1, logical reads 67, ... Table 'SalesPersonQuotaHistory'. Scan count 1, logical reads 4, ... 22. CASE WHEN
? , . CASE WHEN :
USE AdventureWorks2014 GO SELECT BusinessEntityID , Gender , Gender = CASE Gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' ELSE 'Unknown' END FROM HumanResources.Employee SQL Server :
SELECT BusinessEntityID , Gender , Gender = CASE WHEN Gender = 'M' THEN 'Male' WHEN Gender = 'F' THEN 'Female' ELSE 'Unknown' END FROM HumanResources.Employee — TRUE ELSE .
, . , :
IF OBJECT_ID('dbo.GetMailUrl') IS NOT NULL DROP FUNCTION dbo.GetMailUrl GO CREATE FUNCTION dbo.GetMailUrl ( @Email NVARCHAR(50) ) RETURNS NVARCHAR(50) AS BEGIN RETURN SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email)) END SQL Profiler SQL:StmtStarting / SP:StmtCompleted ( XEvents : sp_statement_starting / sp_statement_completed ).
Run the query:
SELECT TOP(10) EmailAddressID , EmailAddress , CASE dbo.GetMailUrl(EmailAddress) --WHEN 'microsoft.com' THEN 'Microsoft' WHEN 'adventure-works.com' THEN 'AdventureWorks' END FROM Person.EmailAddress 10 . :
SELECT TOP(10) EmailAddressID , EmailAddress , CASE dbo.GetMailUrl(EmailAddress) WHEN 'microsoft.com' THEN 'Microsoft' WHEN 'adventure-works.com' THEN 'AdventureWorks' END FROM Person.EmailAddress 20 . , CASE . - . , CASE — .
:
SELECT EmailAddressID , EmailAddress , CASE MailUrl WHEN 'microsoft.com' THEN 'Microsoft' WHEN 'adventure-works.com' THEN 'AdventureWorks' END FROM ( SELECT TOP(10) EmailAddressID , EmailAddress , MailUrl = dbo.GetMailUrl(EmailAddress) FROM Person.EmailAddress ) t 10 .
, CASE :
SELECT DISTINCT CASE WHEN Gender = 'M' THEN 'Male' WHEN Gender = 'M' THEN '...' WHEN Gender = 'M' THEN '......' WHEN Gender = 'F' THEN 'Female' WHEN Gender = 'F' THEN '...' ELSE 'Unknown' END FROM HumanResources.Employee CASE ( ). SQL Server :
DECLARE @i INT = 1 SELECT CASE WHEN @i = 1 THEN 1 ELSE 1/0 END GO DECLARE @i INT = 1 SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END 23. Scalar func
— T-SQL , .
, - , :
USE AdventureWorks2014 GO UPDATE TOP(1) Person.[Address] SET AddressLine2 = AddressLine1 GO IF OBJECT_ID('dbo.isEqual') IS NOT NULL DROP FUNCTION dbo.isEqual GO CREATE FUNCTION dbo.isEqual ( @val1 NVARCHAR(100), @val2 NVARCHAR(100) ) RETURNS BIT AS BEGIN RETURN CASE WHEN (@val1 IS NULL AND @val2 IS NULL) OR @val1 = @val2 THEN 1 ELSE 0 END END :
SET STATISTICS TIME ON SELECT AddressID, AddressLine1, AddressLine2 FROM Person.[Address] WHERE dbo.IsEqual(AddressLine1, AddressLine2) = 1 SELECT AddressID, AddressLine1, AddressLine2 FROM Person.[Address] WHERE (AddressLine1 IS NULL AND AddressLine2 IS NULL) OR AddressLine1 = AddressLine2 SELECT AddressID, AddressLine1, AddressLine2 FROM Person.[Address] WHERE AddressLine1 = ISNULL(AddressLine2, '') SET STATISTICS TIME OFF , , :
SQL Server Execution Times: CPU time = 63 ms, elapsed time = 57 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. , SQL Server , .
— ? Not. SCHEMABINDING :
IF OBJECT_ID('dbo.GetPI') IS NOT NULL DROP FUNCTION dbo.GetPI GO CREATE FUNCTION dbo.GetPI () RETURNS FLOAT WITH SCHEMABINDING AS BEGIN RETURN PI() END GO SELECT dbo.GetPI() FROM Sales.Currency , 1 .
24. VIEWs
- … - . – , .
:
IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL DROP TABLE dbo.tbl GO CREATE TABLE dbo.tbl (a INT, b INT) GO INSERT INTO dbo.tbl VALUES (0, 1) GO IF OBJECT_ID('dbo.vw_tbl', 'V') IS NOT NULL DROP VIEW dbo.vw_tbl GO CREATE VIEW dbo.vw_tbl AS SELECT * FROM dbo.tbl GO SELECT * FROM dbo.vw_tbl :
ab ----------- ----------- 0 1 :
ALTER TABLE dbo.tbl ADD c INT NOT NULL DEFAULT 2 GO SELECT * FROM dbo.vw_tbl :
ab ----------- ----------- 0 1 , , :
EXEC sys.sp_refreshview @viewname = N'dbo.vw_tbl' GO SELECT * FROM dbo.vw_tbl :
abc ----------- ----------- ----------- 0 1 2 .
. , « » AdventureWorks :
ALTER VIEW HumanResources.vEmployee AS SELECT e.BusinessEntityID , p.Title , p.FirstName , p.MiddleName , p.LastName , p.Suffix , e.JobTitle , pp.PhoneNumber , pnt.[Name] AS PhoneNumberType , ea.EmailAddress , p.EmailPromotion , a.AddressLine1 , a.AddressLine2 , a.City , sp.[Name] AS StateProvinceName , a.PostalCode , cr.[Name] AS CountryRegionName , p.AdditionalContactInfo FROM HumanResources.Employee e JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = e.BusinessEntityID JOIN Person.[Address] a ON a.AddressID = bea.AddressID JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID JOIN Person.CountryRegion cr ON cr.CountryRegionCode = sp.CountryRegionCode LEFT JOIN Person.PersonPhone pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT JOIN Person.PhoneNumberType pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT JOIN Person.EmailAddress ea ON p.BusinessEntityID = ea.BusinessEntityID … , ? , :
SELECT BusinessEntityID , FirstName , LastName FROM HumanResources.vEmployee SELECT p.BusinessEntityID , p.FirstName , p.LastName FROM Person.Person p WHERE p.BusinessEntityID IN ( SELECT e.BusinessEntityID FROM HumanResources.Employee e ) :
Table 'EmailAddress'. Scan count 290, logical reads 640, ... Table 'PersonPhone'. Scan count 290, logical reads 636, ... Table 'BusinessEntityAddress'. Scan count 290, logical reads 636, ... Table 'Person'. Scan count 0, logical reads 897, ... Table 'Employee'. Scan count 1, logical reads 2, ... , :
Table 'Person'. Scan count 0, logical reads 897, ... Table 'Employee'. Scan count 1, logical reads 2, ... SQL Server , , .
. , « ». , , … , .
25. CURSORs
SQL Server — . Oracle !
:
DECLARE @BusinessEntityID INT DECLARE cur CURSOR FOR SELECT BusinessEntityID FROM HumanResources.Employee OPEN cur FETCH NEXT FROM cur INTO @BusinessEntityID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE HumanResources.Employee SET VacationHours = 0 WHERE BusinessEntityID = @BusinessEntityID FETCH NEXT FROM cur INTO @BusinessEntityID END CLOSE cur DEALLOCATE cur :
UPDATE HumanResources.Employee SET VacationHours = 0 WHERE VacationHours <> 0 , , . , . , . , 38 . 600. — 200.
SQL Server — !
26. STRING_CONCAT
— , . ?
STRING_CONCAT , … 2016 , , SQL Server . - ?
:
IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (i CHAR(1)) INSERT INTO #t VALUES ('1'), ('2'), ('3') «» — :
DECLARE @txt VARCHAR(50) = '' SELECT @txt += i FROM #t SELECT @txt -------- 123 , MS , , :
DECLARE @txt VARCHAR(50) = '' SELECT @txt += i FROM #t ORDER BY LEN(i) SELECT @txt -------- 3 , , . : CLR , UPDATE , , , … .
, 90% XML:
SELECT [text()] = i FROM #t FOR XML PATH('') -------- 123 . -, - , :
SELECT [name], STUFF(( SELECT ', ' + c.[name] FROM sys.columns c WHERE c.[object_id] = t.[object_id] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') FROM sys.objects t WHERE t.[type] = 'U' ------------------------ ------------------------------------ ScrapReason ScrapReasonID, Name, ModifiedDate Shift ShiftID, Name, StartTime, EndTime XML , :
:
SELECT [name], STUFF(( SELECT ', ' + c.[name] FROM sys.columns c WHERE c.[object_id] = t.[object_id] FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '') FROM sys.objects t WHERE t.[type] = 'U' . value :
SELECT t.name , STUFF(( SELECT ', ' + c.name FROM sys.columns c WHERE c.[object_id] = t.[object_id] FOR XML PATH('')), 1, 2, '') FROM sys.objects t WHERE t.[type] = 'U' 
, «». :
SELECT t.name , STUFF(( SELECT ', ' + CHAR(13) + c.name FROM sys.columns c WHERE c.[object_id] = t.[object_id] FOR XML PATH('')), 1, 2, '') FROM sys.objects t WHERE t.[type] = 'U' , , , .
: , value , value('(./text())[1]'... .
27. SQL Injection
sql injection , . :
DECLARE @param VARCHAR(MAX) SET @param = 1 DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT TOP(5) name FROM sys.objects WHERE schema_id = ' + @param PRINT @SQL EXEC (@SQL) :
SELECT TOP(5) name FROM sys.objects WHERE schema_id = 1 - :
SET @param = '1; select ''hack''' :
SELECT TOP(5) name FROM sys.objects WHERE schema_id = 1; select 'hack' , sql injection , - «». — :)
String.Format ( ), , sql injection :
using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = @"Server=.;Database=AdventureWorks2014;Trusted_Connection=true"; conn.Open(); SqlCommand command = new SqlCommand( string.Format("SELECT TOP(5) name FROM sys.objects WHERE schema_id = {0}", value), conn); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) {} } } , sp_executesql :
DECLARE @param VARCHAR(MAX) SET @param = '1; select ''hack''' DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT TOP(5) name FROM sys.objects WHERE schema_id = @schema_id' PRINT @SQL EXEC sys.sp_executesql @SQL , N'@schema_id INT' , @schema_id = @param , - .
:
using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = @"Server=.;Database=AdventureWorks2014;Trusted_Connection=true"; conn.Open(); SqlCommand command = new SqlCommand( "SELECT TOP(5) name FROM sys.objects WHERE schema_id = @schema_id", conn); command.Parameters.Add(new SqlParameter("schema_id", value)); ... } , …
38
— 8- . , T-SQL . , .
, « » SQL Server , , - . .
:
SQL Server: Useful Tips for Newbies
Video
, «» russianVC : . .
?
. , .
, parameter sniffing , : , SSMS .
, , .
Source: https://habr.com/ru/post/315142/
All Articles