您可能（或可能不）知道的5条便捷的Transact-SQL技巧

介绍 (Introduction)

Even if you are a SQL veteran, actually especially if you are, you sometimes get really stuck doing something in the old way not realizing that in newer versions of SQL Server some handy functions have been added that can make your life easier.

即使您是SQL的资深人士，实际上尤其是您，有时您仍然会以旧的方式执行某些工作，而没有意识到在新版本SQL Server中添加了一些方便的功能，这些功能可以使您的生活更轻松。

In this article, I will mention a couple of these which appeared in SQL Server 2008 or later versions or might have even been there before but most people just didn’t know about.

在本文中，我将提到其中的一些，它们出现在SQL Server 2008或更高版本中，甚至可能以前都曾经出现过，但是大多数人只是不知道。

逻辑条件 (Logical condition)

In regular programming languages, such as VB or C#, you have long since been able to use the IIF statement. You are even able to use this in Excel. But in SQL you have always had to use IF ..ELSE or a CASE to perform a logical condition operation.

在常规的编程语言（例如VB或C＃）中，您早就可以使用IIF语句了。 您甚至可以在Excel中使用它。 但是在SQL中，您始终必须使用IF ..ELSE或CASE来执行逻辑条件操作。

You can now use this in SQL Server. For example:

现在，您可以在SQL Server中使用它。 例如：

 
DECLARE @SON varchar(20) = 'LUKE'
 
SELECT IIF(@SON = 'LUKE', 'I AM YOUR FATHER', 'I AM NOT YOUR FATHER')
 

The first parameter is the condition, the second parameter is what will be returned if the condition is true and the third parameter is what will be returned if the condition is false.

第一个参数是条件，第二个参数是条件为true时将返回的参数，第三个参数是条件为false时将返回的参数。

In this case the result will be as follows:

在这种情况下，结果将如下所示：

级联 (Concatenation)

Ok so many of you may think this is not a big deal, you can already do string concatenation by using this syntax here.

好的，很多人可能认为这没什么大不了的，您已经可以在这里使用此语法进行字符串连接了。

 
Select name + '''s birthday was ' + birthdate from person
 

But of course there is a bug here.

但当然这里有一个错误。

To get this to work you will have to perform an explicit conversion of the date such as:

为了使它生效，您将必须对日期进行明确的转换，例如：

 
Select name + '''s birthday was ' + CONVERT(VARCHAR(12),birthdate,113) from person
 

Which will return:

哪个会返回：

What’s nice about the CONCAT function is that it does the implicit conversion. So you don’t have to convert or cast to a string before adding it.

CONCAT函数的优点在于它执行隐式转换。 因此，您不必在添加字符串之前将其转换或转换为字符串。

 
SELECT CONCAT(name, '''s birthday was ', birthdate) FROM person
 

The CONCAT function can concatenate up to 255 characters. Null is converted to empty string.

CONCAT函数最多可连接255个字符。 Null转换为空字符串。

复合运算符 (Compound Operators)

You have been able to use compound operators in other languages forever. Where you can just declare the parameter and increment or decrement by a number in a single command.

您已经能够永久使用其他语言的复合运算符。 您只需在单个命令中声明参数并按数字递增或递减的位置。

 
int cnt = 0;
cnt += 2;
Console.WriteLine("The count is: {0}",cnt);
 

But in SQL you had to assign the variable and its increment back to the variable.

但是在SQL中，您必须将变量及其增量分配回该变量。

 
DECLARE @cnt int = 0
 
WHILE @cnt < 10
	BEGIN
	PRINT @cnt
	SELECT @cnt = @cnt + 1
	END
GO
 

But now from SQL Server 2008 onwards you are able to use compound operators to set an original value to the result of the expression

但是现在从SQL Server 2008开始，您就可以使用复合运算符为表达式的结果设置原始值

 
DECLARE @cnt int = 0
 
WHILE @cnt < 10
	BEGIN
	PRINT @cnt
	SELECT @cnt+=2;
	END
GO
 

The following compound operators are available:

可以使用以下复合运算符：

+=	Adds the value to the original value and sets the new value to the result.
-=	Subtracts the original value and sets the new value by subtracting original with the new one.
*=	Multiplies by an amount and sets the original value to the result.
/=	Divides by an amount and sets the original value to the result.
%=	Divides by an amount and sets the original value to the modulo.
&=	Performs a bitwise AND and sets the original value to the result.
^=	Performs a bitwise exclusive OR and sets the original value to the result.
|=	Performs a bitwise OR and sets the original value to the result.

+ =	将值添加到原始值，然后将新值设置为结果。
-=	减去原始值并通过用新值减去原始值来设置新值。
* =	乘以一个量并将原始值设置为结果。
/ =	除以一个数量并将原始值设置为结果。
％=	除以一个量并将原始值设置为模。
＆=	执行按位与，并将原始值设置为结果。
^ =	执行按位异或，并将原始值设置为结果。
| =	执行按位“或”运算并将原始值设置为结果。

使用一个INSERT命令插入多行 (Insert multiple rows with one INSERT command)

The Table Value Constructor

表值构造器

For ages you had to create one command for each row you wanted to insert unless you were using a SELECT INTO your command typically had to look something like this:

很久以来，除非要使用SELECT INTO，否则您必须为要插入的每一行创建一个命令，除非您的命令通常看起来像这样：

 
CREATE TABLE items (item_id int, item_name varchar(10))
 
INSERT INTO items VALUES (1,'Hat')
INSERT INTO items VALUES (2,'Sock')
INSERT INTO items VALUES (3,'Shoe')
INSERT INTO items VALUES (4,'Coat')
INSERT INTO items VALUES (5,'Shirt')
 

This has now been made somewhat easier, allowing you to not have to repeat the INSERT INTO portion of the command, but only specify the values you’d like to insert by using the Table Value Constructor. Such as this:

现在，此操作已变得更容易一些，使您不必重复命令的INSERT INTO部分，而只需使用表值构造函数指定要插入的值。 如：

 
INSERT INTO items VALUES (1,'Hat'),
				(2,'Sock'),
				(3,'Shoe'),
				(4,'Coat'),
				(5,'Shirt')
 
 

You can also specify a query as a value, but it has to return a scalar value such as this:

您还可以将查询指定为值，但是它必须返回这样的标量值：

 
INSERT INTO items VALUES (1,'Hat'),
			(2,'Sock'),
			(3,'Coat'),
			(4, (select name from product where product_id = 10))
 
GO
 

The table value constructor is limited to 1000 rows.

表值构造函数限制为1000行。

The Batch Replicator

批处理复制器

You also have the option to insert the same row multiple times by using the batch replicator option of the batch separator.

您还可以选择使用批处理分隔符的批处理复制器选项多次插入同一行。

The batch separator is GO by default. The purpose of this command is to separate the code in a script into batches. Such as:

批处理分隔符默认为GO。 此命令的目的是将脚本中的代码分为几批。 如：

 
USE AdventureWorks2012
GO
INSERT INTO items VALUES (1,'Hat')
GO
 

In this example, the USE Adventureworks2012 is the first batch and the INSERT command is the second batch.

在此示例中，USE Adventureworks2012是第一批，而INSERT命令是第二批。

So, now if you wanted to add the same row to the table multiple times you can specify a number of times that it should be executed such as:

因此，现在，如果您想多次向表添加同一行，则可以指定应执行的行数，例如：

 
--Insert 1 row 10 times
INSERT INTO items VALUES (1,'Hat')
GO 10
 

In this case, the same row will be inserted 10 times. It may not seem very useful, but it is very handy if you have to generate large amounts of data for performance testing etc. Of course, your batch doesn’t need to be as simple as this example, you can modify it to insert data with different data types and derived values to create good quality sample data.

在这种情况下，同一行将被插入10次。 它可能看似不太有用，但是如果您必须生成大量数据以进行性能测试等时非常方便。当然，批处理不需要像此示例那样简单，您可以对其进行修改以插入数据具有不同的数据类型和派生值以创建高质量的样本数据。

It’s worth noting that the batch separate is in actual fact NOT transact-SQL, it is a command which gets interpreted by sqlcmd, osql or SQL Server Management Studio.

值得注意的是，批处理分隔实际上不是Transact-SQL，它是由sqlcmd，osql或SQL Server Management Studio解释的命令。

When SQL Server Management Studio encounters a GO, it knows that it needs to send the preceding batch of code to the SQL Server instance. Using the replicator, simply tells the utility to send the batch multiple times.

当SQL Server Management Studio遇到GO时，它知道需要将前一批代码发送到SQL Server实例。 使用复制器，只需告诉实用程序多次发送批处理。

模板浏览器 (Template explorer)

SQL Server ships with a whole bunch of templates for common commands, which allows you to get the correct syntax to perform a certain task without having to google it. This is built into SQL Server Management Studio and can be viewed in the template explorer.

SQL Server附带了一堆用于常见命令的模板，这使您能够获得正确的语法来执行特定任务，而无需进行谷歌搜索。 它内置在SQL Server Management Studio中，可以在模板资源管理器中查看。

You can access the Template Explorer from the View Menu:

您可以从“查看”菜单访问“模板资源管理器”：

Which will open the template browser window, which displays all the available templates

将打开模板浏览器窗口，其中显示所有可用模板

It also allows you to create your own templates, which is really handy if you have scripts that you use frequently. You can create your own templates by right clicking on the SQL Server Templates Node in the Template Browser and selecting the new option. You can then create a new folder or a new template.

它还允许您创建自己的模板，如果您有经常使用的脚本，这将非常方便。 您可以通过右键单击“模板浏览器”中的“ SQL Server模板”节点并选择新选项来创建自己的模板。 然后，您可以创建一个新文件夹或新模板。

结论 (Conclusion)

SQL Server has a myriad of tools and functions that most people are not aware of. These are just a couple of things I came across, but there are sure to be tons more. It is definitely worthwhile to sometimes just poke around and see what pops up that you haven’t seen before, especially for newer versions as SQL Server and the Transact-SQL language is ever improving.

SQL Server具有许多大多数人都不知道的工具和功能。 这些只是我遇到的几件事，但是肯定还有更多。 有时只是四处看看，看看以前没有出现过的弹出窗口绝对是值得的，尤其是对于SQL Server和Transact-SQL语言不断改进的较新版本。

Consider these free tools for SQL Server that improve database developer productivity.

考虑使用这些免费SQL Server工具来提高数据库开发人员的生产力。

参考资料 (References)

• Batch Replicator批量复制器
• Table Value Constructor表值构造器
• Template Explorer模板浏览器

 

翻译自: https://www.sqlshack.com/5-handy-transact-sql-tips-may-may-not-know/