Which is preferred SELECT or SET

Question

When assigning a variable, is there a functional difference between set and select?

I often write scripts that have to iterate through processes, and on each iteration I update a variable with a value.

For example, my company's product has multiple servers, on each server we have a certain number of databases that our clients data resides in. Each database has between 5 and 50 clients. A table in a primary database indicates which of the individual databases each client is on. Today I found a problem with the primary key on one table, and we need to modify the primary key to add a column to it. The table on each database may have several hundred thousand records, so we expect the updates to take some time. We need to do these overnight to avoid performance issues. So I've written the following script to iterate through the process on each database (I'll execute it separately on each server):

DECLARE @DBTABLE TABLE
(
    TableID                 INT IDENTITY PRIMARY KEY NOT NULL,
    DbName                  VARCHAR(50) NOT NULL,
    ServerName              VARCHAR(50) NOT NULL,
    ProcFlag                INT NOT NULL DEFAULT 0
)

INSERT INTO @DBTABLE (DbName, ServerName)
SELECT      DISTINCT DbName, ServerName
FROM        PrimaryDatabase.dbo.Cients WITH(NOLOCK)
WHERE       ClientInactive = 0
    AND     ServerName = @@SERVERNAME

DECLARE     @TABLETEST  INT
DECLARE     @TABLEID    INT
DECLARE     @DBNAME     VARCHAR(50)
DECLARE     @SERVERNAME VARCHAR(50)
DECLARE     @VAR_SQL    VARCHAR(MAX)

SET     @TABLETEST = (SELECT COUNT(*) FROM @DBTABLE WHERE ProcFlag = 0)
WHILE   @TABLETEST > 0
BEGIN
    SET     @TABLEID =      (SELECT MIN(TableID) FROM @DBTABLE WHERE ProcFlag = 0)
    SET     @DBNAME =       (SELECT DbName FROM @DBTABLE WHERE TableID = @TABLEID)
    SET     @SERVERNAME =   (SELECT ServerName FROM @DBTABLE WHERE TableID = @TABLEID)

    SET     @VAR_SQL = '

        ALTER TABLE ' + @DBNAME + '.dbo.ClientDealTable DROP CONSTRAINT [PK_ClientDealTable]
        ALTER TABLE ' + @DBNAME + '.dbo.ClientDealTable ADD CONSTRAINT [PK_ClientDealTable] PRIMARY KEY CLUSTERED ([ClientID] ASC, [DealNumber] ASC, [DealDate] ASC)
    '

    EXEC(@VAR_SQL)

    UPDATE      @DBTABLE SET ProcFlag = 1 WHERE TableID = @TABLEID
    SET         @TABLETEST = (SELECT COUNT(*) FROM @DBTABLE WHERE ProcFlag = 0)

END

Is SET or SELECT the preferred option here, or does it really matter? Is there a performance difference?

Firstly, use a cursor. You're just trying to hide the logical equivalent. If you need a cursor, use one. Next, stop splattering nolock in your code. This is not a best practice and not a magic option. It has consequences. Lastly, assigning a scalar value via one or the other does have a potential impact that you need to be aware of. See the duplicate. — SMor, Feb 19 '20 at 21:24
Does this answer your question? [SET versus SELECT when assigning variables?](https://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables) — SMor, Feb 19 '20 at 21:24
I'm curious as to the consequences of using nolock. It's been drilled into my head to use it constantly (or readuncommitted). We frequently run into blocking issues on tables and my company's standard practice is to use nolock or readuncommitted everywhere possible.= — J.M. Haynes, Feb 19 '20 at 22:51
@SMor, thanks for the link (not sure why it didn't turn up in my search prior to posting). The fact that SET will assign a NULL but SELECT will leave the prior value assigned to the variable is a big thing. I wasn't aware of that before. So, thank you. — J.M. Haynes, Feb 19 '20 at 22:58
Rather than using `set`/`select` pairs twice for `@DBNAME` and `@SERVERNAME` you can get both values in a single statement: `select @DBNAME = DbName, @SERVERNAME = ServerName from @DBTABLE where TableID = @TABLEID;`. That removes one (redundant) query from the loop. Aside: You also appear to be using an exact `count` for `@TABLETEST` when an `exists` query would suffice: `while exists (SELECT 42 FROM @DBTABLE WHERE ProcFlag = 0)`. — HABO, Feb 20 '20 at 03:16

score 3 · Answer 1 · answered Feb 19 '20 at 21:25

SET can only set the value of a single variable. using SELECT you can set the value of any number of variables.

But in your code I wouldn't use either. I would do this without looping. Not to mention there is a mountain less code to write. This should do the same thing and is a lot simpler.

DECLARE @VAR_SQL VARCHAR(MAX)

SELECT @VAR_SQL = 'ALTER TABLE ' + QUOTENAME(DbName) + '.dbo.ClientDealTable DROP CONSTRAINT [PK_ClientDealTable];ALTER TABLE ' + QUOTENAME(DbName) + '.dbo.ClientDealTable ADD CONSTRAINT [PK_ClientDealTable] PRIMARY KEY CLUSTERED ([ClientID] ASC, [DealNumber] ASC, [DealDate] ASC)'
FROM PrimaryDatabase.dbo.Cients --WITH(NOLOCK)
WHERE ClientInactive = 0
    AND ServerName = @@SERVERNAME

exec sp_executesql @VAR_SQL

Which is preferred SELECT or SET

1 Answers1