SELECT INTO table variable fails with Msg 102 because SELECT INTO creates its target table and a table variable must be declared first. This post shows two working fixes, a declared table variable and a temp table. It also compares them on rollback, dynamic SQL and row estimates.
Counting Ranked-Choice Votes With a Loop and a Table
Four people rank three topics and nobody has a majority. Here is a small loop that eliminates the weakest topic, moves its ballots and logs every round.
The VALUES Table Constructor: Using a List as a Table
Use a VALUES table constructor for inline mappings and joins, understand the INSERT row limit, and move larger reusable lists into tables.
SQL Puzzle – Schema and Table Creation – Answer Without Running Code
This Table Creation puzzle asks for an explanation before you run its code. I suggest examining the batch separators carefully.
SCHEMABINDING View: What You Can Change in the Table
A SCHEMABINDING view locks the columns it uses and leaves the other columns of the table free. This post tests which changes SQL Server blocks, shows the messages and lists the bound columns with a query. It also shows how to change a bound column inside one transaction.
What is SCHEMABINDING in SQL Server Views? – Interview Question of the Week #245
SCHEMABINDING keywords prevent tables used in the views to make any such modifications that can affect the view’s definition.
Drop Multiple Columns in One ALTER TABLE Statement
To drop multiple columns, list them after one DROP COLUMN in one ALTER TABLE statement. This post tests the syntax on a table with a default, a check constraint and an index, shows the error that a dependent object causes and drops the constraints and columns together. It also explains why the disk space doesn’t return until a rebuild.