Product:
Microsoft SQL server 2016
Issue:
Get a warning when you create a table with a primary key.
Warning! The maximum key length for a clustered index is 900 bytes. The index ‘xyz’ has maximum length of 8024 bytes. For some combination of large values, the insert/update operation will fail.
CREATE TABLE [fun_table] ([the_name] [nvarchar](100) NULL, [the_value] [sql_variant] NOT NULL, -- will give the error CONSTRAINT [PK_thevalue] PRIMARY KEY CLUSTERED ([the_value] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Suggested solution:
Change the sql_variant to a nvarchar() instead if you are going to use it in a index. This is a warning that if you store more than 900 bytes in the column the_value, it will be truncated at the index/primary key.
More information:
From the internet – but this facts change with what version of Microsoft SQL server you are using.
As a general rule, you should avoid using SQL Server’s sql_variant data type. sql_variant is limited:
- Variants can’t be part of a primary or foreign key in SQL server before 2005.
- Variants can’t be part of a computed column.
- Variants won’t work with LIKE in a WHERE clause.
- OLE DB and ODBC providers automatically convert variants to nvarchar(4000)
To avoid problems, always explicitly convert sql_variant data types as you use them.
But some people claim that SQL_VARIANT is a fair bit faster than going trough VARCHAR conversions for your columns.
https://www.visualbasicplanet.info/sql-server-2000/sqlvariant.html
https://stackoverflow.com/questions/9039455/should-i-use-sql-variant-data-type