What does the FOR keyword do in this SQL Server ALTER TABLE clause?

Question

I'm not sure what FOR is doing in this SQL Server snippet.

ALTER TABLE [dbo].[TableName] 
    ADD DEFAULT (NEXT VALUE FOR [dbo].[TableName_Seq]) FOR [TableName_Key]

When I try to google for FOR clause the only meaningful result I got was this, but I don't think it's relevant

[NEXT VALUE FOR (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver15) — Thom A, Apr 17 '21 at 14:21
[ALTER TABLE (Transact-SQL) - Examples: Adding Columns and Constraints - D. Adding a DEFAULT constraint to an existing column](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#d-adding-a-default-constraint-to-an-existing-column) — Thom A, Apr 17 '21 at 14:24
There are **two** `FOR` - which one is the one that's unclear to you?? — marc_s, Apr 17 '21 at 14:24

Dan Guzman · Answer 1 · 2021-04-17T14:26:08.463

3

FOR is a keyword of the NEXT VALUE FOR function. It is used to return the next value from the specified sequence object.

FOR is also a keyword of the DEFAULT constraint clause, specifying the target column name of the constraint.

In the context of the DDL in your question, the function result is the default value automatically assign the value when a value is not explicitly specified in INSERT statements.

BTW, it's a best practice to name constraints rather than relying on auto-generated names. This makes subsequent schema modifications easier.

edited Apr 17 '21 at 14:26

answered Apr 17 '21 at 14:21

Dan Guzman

43,250
3
46
71

True - **if** that's the `FOR` he's asking about - there are two `FOR` in that statement, though .... – marc_s Apr 17 '21 at 14:25
1

Good point @marc_s, added that to my answer. – Dan Guzman Apr 17 '21 at 14:28
Another way of putting it is it's just one of those unnecessary and ambiguous (context-dependent) words that SQL loves, like `insert INTO` `fetch NEXT ... ONLY` `left OUTER join` – Charlieface Apr 17 '21 at 22:42
Thank you @Charlieface. – Jimmy Vo Apr 19 '21 at 21:34

score 1 · Answer 2 · answered Apr 17 '21 at 14:25

FOR by itself has no specific meaning, it depends on context.

In this case, there are 2 usages of FOR:

NEXT VALUE FOR function, which is used to generate the next value from a sequence.
ALTER TABLE [table_name] ADD [table_constraint], where table_constraint is of the form DEFAULT constant_expression FOR column. So it's adding a default constraint for the TableName_Key column, and the value for that default constraint is coming from the TableName_Seq sequence.

What does the FOR keyword do in this SQL Server ALTER TABLE clause?

2 Answers2