To keep only digits in a column, a CHECK constraint with NOT LIKE and a character range works, but it has two holes. It accepts an empty string and digits from other scripts. A demo closes both and shows NULL, trust and fixed length codes.
SQL SERVER – Capturing INSERT Timestamp in Table
A default can insert timestamp values when the caller omits a datetime column. I use a session-local CURRENT_TIMESTAMP example.
How to Add Constraint With No Validation? – Interview Question of the Week #299
Question: How to Add Constraint With No Validation?
Answer: I got this question from one of my clients of who is an Oracle user.
Check Constraint on Identity Column: Why Inserts Fail
A check constraint on identity column values can make every insert fail, because the identity moves on whether or not the row is accepted. This post reproduces the failure, shows that the failed attempts still use up identity values, and lists every such constraint in a database with one query.
Create Table From Another Table: What SELECT INTO Copies
To create table from another table in SQL Server, use SELECT INTO with WHERE 1 = 2 or TOP (0) for the structure alone. This post builds a demo table and compares it with its copy column by column. It shows the identity trap, the computed column that becomes plain, and the statements that add keys, defaults and indexes back.
Partition Switch in SQL Server: Move a Million Rows in a Moment
A partition switch moves a whole partition from one table to another without copying a single row. This post builds a partitioned table with a million rows, switches the January partition out and back in, and shows that only metadata changes. It lists the five errors that stop a switch, with the real message numbers, and ends with the sliding window pattern.
Nonclustered Primary Key – SQL in Sixty Seconds #119
I will describe in detail a bit later on during this blog post. Today we are going to learn that it is possible to have a nonclustered primary key.