导致我相信,对于T-SQL中的单个变量赋值,set是处理问题的最佳方法,其原因有两个:

  • 这是用于变量分配的ANSI标准
  • 实际上比执行SELECT(对于单个变量)更快

  • 所以...
    SELECT @thingy = 'turnip shaped'
    

    变成
    SET @thingy  = 'turnip shaped'
    

    但是,速度有多快?我真的会注意到差异吗?

    最佳答案

    单次运行SET更快。您可以很容易地证明这一点。它是否起作用取决于您,但是我更喜欢SET,因为如果所有代码都在执行的话,我看不到SELECT的意义。我更喜欢将SELECT限制在表, View 等的SELECT语句中。

    这是一个示例脚本,运行次数设置为1:

    SET NOCOUNT ON
    
    DECLARE @runs int
    DECLARE @i int, @j int
    SET @runs = 1
    SET @i = 0
    SET @j = 0
    
    DECLARE @dtStartDate datetime, @dtEndDate datetime
    
    
    WHILE @runs > 0
        BEGIN
            SET @j = 0
            SET @dtStartDate = CURRENT_TIMESTAMP
            WHILE @j < 1000000
                BEGIN
                    SET @i = @j
                    SET @j = @j + 1
                END
            SELECT @dtEndDate = CURRENT_TIMESTAMP
            SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SET_MILLISECONDS
    
    
            SET @j = 0
            SET @dtStartDate = CURRENT_TIMESTAMP
            WHILE @j < 1000000
                BEGIN
                    SELECT @i = @j
                    SET @j = @j + 1
                END
            SELECT @dtEndDate = CURRENT_TIMESTAMP
            SELECT DATEDIFF(millisecond, @dtStartDate, @dtEndDate) AS SELECT_MILLISECONDS
    
            SET @runs = @runs - 1
        END
    

    结果:

    运行#1:

    SET_MILLISECONDS

    5093

    SELECT_MILLISECONDS

    5186

    运行#2:

    SET_MILLISECONDS

    4876

    SELECT_MILLISECONDS

    5466

    运行#3:

    SET_MILLISECONDS

    4936

    SELECT_MILLISECONDS

    5453

    运行#4:

    SET_MILLISECONDS

    4920

    SELECT_MILLISECONDS

    5250

    运行#5:

    SET_MILLISECONDS

    4860

    SELECT_MILLISECONDS

    5093

    奇怪的是,如果您将运行次数提高到10,则SET开始落后。

    这是10次运行结果:

    SET_MILLISECONDS

    5140

    SELECT_MILLISECONDS

    5266

    SET_MILLISECONDS

    5250

    SELECT_MILLISECONDS

    5466

    SET_MILLISECONDS

    5220

    SELECT_MILLISECONDS

    5280

    SET_MILLISECONDS

    5376

    SELECT_MILLISECONDS

    5280

    SET_MILLISECONDS

    5233

    SELECT_MILLISECONDS

    5453

    SET_MILLISECONDS

    5343

    SELECT_MILLISECONDS

    5423

    SET_MILLISECONDS

    5360

    SELECT_MILLISECONDS

    5156

    SET_MILLISECONDS

    5686

    SELECT_MILLISECONDS

    5233

    SET_MILLISECONDS

    5436

    SELECT_MILLISECONDS

    5500

    SET_MILLISECONDS

    5610

    SELECT_MILLISECONDS

    5266

    关于performance - 在T-SQL中SELECT与SET的速度,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/189588/

    10-11 23:02