SQL Server Sysobjects Types (Version 2019)

February 26, 2020

MS SQL Server stores the information about the objects (tables, views, functions, etc) stored in the database in a table, which is accessible by selecting from the sys.objects view. The type of object is given by the column “type”

I collected this cross-version information from Microsoft Docs online, but fixed a couple of historical mistakes there.

Object type: 2008 2012 2016 2019
sys.objects.type
AF Aggregate function (CLR) x x x x
C CHECK constraint x x x x
D DEFAULT (constraint or stand-alone) x x x x
EC Edge constraint x
ET External Table x x
F FOREIGN KEY constraint x x x x
FN SQL scalar function x x x x
FS Assembly (CLR) scalar function x x x x
FT Assembly (CLR) table-valued function x x x x
IF SQL inline table-valued function x x x x
IT Internal table x x x x
P SQL stored procedure x x x x
PC Assembly (CLR) stored procedure x x x x
PG Plan guide x x x x
PK PRIMARY KEY constraint x x x x
R Rule (old-style, stand-alone) x x x x
RF Replication-filter-procedure x x x x
S System base table x x x x
SN Synonym x x x x
SO Sequence object x x x
SQ Service queue x x x x
TA Assembly (CLR) DML trigger x x x x
TF SQL table-valued-function x x x x
TR SQL DML trigger x x x x
TT Table type x x x x
U Table (user-defined) x x x x
UQ UNIQUE constraint x x x x
V View x x x x
X Extended stored procedure x x x x

The sys.objects.type_desc column contains the description corresponding to the type:

type sys.objects.type_desc
AF AGGREGATE_FUNCTION
C CHECK_CONSTRAINT
D DEFAULT_CONSTRAINT
EC
ET
F FOREIGN_KEY_CONSTRAINT
FN SQL_SCALAR_FUNCTION
FS CLR_SCALAR_FUNCTION
FT CLR_TABLE_VALUED_FUNCTION
IF SQL_INLINE_TABLE_VALUED_FUNCTION
IT INTERNAL_TABLE
P SQL_STORED_PROCEDURE
PC CLR_STORED_PROCEDURE
PG PLAN_GUIDE
PK PRIMARY_KEY_CONSTRAINT
R RULE
RF REPLICATION_FILTER_PROCEDURE
S SYSTEM_TABLE
SN SYNONYM
SO SEQUENCE_OBJECT
SQ SERVICE_QUEUE
TA CLR_TRIGGER
TF SQL_TABLE_VALUED_FUNCTION
TR SQL_TRIGGER
TT TYPE_TABLE
U USER_TABLE
UQ UNIQUE_CONSTRAINT
V VIEW
X EXTENDED_STORED_PROCEDURE

My previous post on this topic only covered SQL Server up to version 2008.


Length of UTF-8 VARCHAR in SQL Server

February 21, 2020

Foreword

For as long as I can remember, a VARCHAR (or CHAR) was always defined as “1 character equals 1 byte”. Different character sets (code pages) where implemented as COLLATIONs, so that you had basic database support for internationalization.

Then came Unicode, and we got NVARCHAR strings (or NCHAR), where the rule was “1 character equals 2 bytes”, and we could store any text from around the world without bothering with code pages, encodings, etc. The .Net framework brought us the string class with similar features and the world was beautiful.

Then, in 2001, came Unicode 3.1 and needed more space:

For the first time, characters are encoded beyond the original 16-bit codespace or Basic Multilingual Plane (BMP or Plane 0). These new characters, encoded at code positions of U+10000 or higher, are synchronized with the forthcoming standard ISO/IEC 10646-2. For further information, see Article IX, Relation to 10646. Unicode 3.1 and 10646-2 define three new supplementary planes.

These additional planes were immediately supported in SQL Server 2012. From now on, using an *_SC collation, NVARCHARs could be 2 or 4 bytes per character.

In C#, the StringInfo class handles supplementary planes, but it seems, they are still a bit behind:

Starting with the .NET Framework 4.6.2, character classification is based on The Unicode Standard, Version 8.0.0. For the .NET Framework 4 through the .NET Framework 4.6.1, it is based on The Unicode Standard, Version 6.3.0. In .NET Core, it is based on The Unicode Standard, Version 8.0.0.

(For the record, the current Unicode version is 12.1, and 13.0 is going to be released soon)

UTF-8 Collations

So now SQL Server 2019 supports UTF-8-enabled collations.

A question on SO quoted the documentation as

A common misconception is to think that CHAR(n) and VARCHAR(n), the n defines the number of characters. But in CHAR(n) and VARCHAR(n) the n defines the string length in bytes (0-8,000). n never defines numbers of characters that can be stored

(emphasis mine) which confused me a little bit, and the quote continues

The misconception happens because when using single-byte encoding, the storage size of CHAR and VARCHAR is n bytes and the number of characters is also n.

(emphasis mine).

This got me investigating, and I had a look into this issue. I create a UTF8-enabled database with a table with all kinds of N/VARCHAR columns

CREATE DATABASE [test-sc] COLLATE Latin1_General_100_CI_AI_KS_SC_UTF8

CREATE TABLE [dbo].[UTF8Test](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [VarcharText] [varchar](50) COLLATE Latin1_General_100_CI_AI NULL,
  [VarcharTextSC] [varchar](50) COLLATE Latin1_General_100_CI_AI_KS_SC NULL,
  [VarcharUTF8] [varchar](50) COLLATE Latin1_General_100_CI_AI_KS_SC_UTF8 NULL,
  [NVarcharText] [nvarchar](50) COLLATE Latin1_General_100_CI_AI_KS NULL,
  [NVarcharTextSC] [nvarchar](50) COLLATE Latin1_General_100_CI_AI_KS_SC NULL,
  [NVarcharUTF8] [nvarchar](50) COLLATE Latin1_General_100_CI_AI_KS_SC_UTF8 NULL
)

I inserted test data from various Unicode ranges

INSERT INTO [dbo].[UTF8Test] ([VarcharText],[VarcharTextSC],[VarcharUTF8],[NVarcharText],[NVarcharTextSC],[NVarcharUTF8])
    VALUES ('a','a','a','a','a','a')
INSERT INTO [dbo].[UTF8Test] ([VarcharText],[VarcharTextSC],[VarcharUTF8],[NVarcharText],[NVarcharTextSC],[NVarcharUTF8])
    VALUES ('ö','ö','ö',N'ö',N'ö',N'ö')
-- U+56D7
INSERT INTO [dbo].[UTF8Test] ([VarcharText],[VarcharTextSC],[VarcharUTF8],[NVarcharText],[NVarcharTextSC],[NVarcharUTF8])
    VALUES (N'囗',N'囗',N'囗',N'囗',N'囗',N'囗')
-- U+2000B
INSERT INTO [dbo].[UTF8Test] ([VarcharText],[VarcharTextSC],[VarcharUTF8],[NVarcharText],[NVarcharTextSC],[NVarcharUTF8])
    VALUES (N'𠀋',N'𠀋',N'𠀋',N'𠀋',N'𠀋',N'𠀋')

then selected the lengths and data lengths of each text field

SELECT TOP (1000) [Id]
    ,[VarcharText],[VarcharTextSC],[VarcharUTF8]
    ,[NVarcharText],[NVarcharTextSC],[NVarcharUTF8]
FROM [test-sc].[dbo].[UTF8Test]
SELECT TOP (1000) [Id]
    ,LEN([VarcharText]) VT,LEN([VarcharTextSC]) VTSC
    ,LEN([VarcharUTF8]) VU
    ,LEN([NVarcharText]) NVT,LEN([NVarcharTextSC]) NVTSC
    ,LEN([NVarcharUTF8]) NVU
FROM [test-sc].[dbo].[UTF8Test]
SELECT TOP (1000) [Id]
    ,DATALENGTH([VarcharText]) VT,DATALENGTH([VarcharTextSC]) VTSC
    ,DATALENGTH([VarcharUTF8]) VU
    ,DATALENGTH([NVarcharText]) NVT,DATALENGTH([NVarcharTextSC]) NVTSC
    ,DATALENGTH([NVarcharUTF8]) NVU
FROM [test-sc].[dbo].[UTF8Test]

Select Lengths.png

I was surprised to find that the old mantra “a VARCHAR only stores single byte characters” needs to be revised when using UTF8 collations.

Table data only

Note that only table columns are associated with collations, but not T-SQL variables, as you cannot declare a collation on a variable

SELECT @VarcharText = [VarcharText],@NVarcharText = [NVarcharText]
FROM [test-sc].[dbo].[UTF8Test]
WHERE [Id] = 4
SELECT @VarcharText, Len(@VarcharText), DATALENGTH(@VarcharText)
    , @NVarcharText, Len(@NVarcharText), DATALENGTH(@NVarcharText)

SELECT @VarcharText = [VarcharTextSC], @NVarcharText = [NVarcharTextSC]
FROM [test-sc].[dbo].[UTF8Test]
WHERE [Id] = 4
SELECT @VarcharText, Len(@VarcharText), DATALENGTH(@VarcharText)
    , @NVarcharText, Len(@NVarcharText), DATALENGTH(@NVarcharText)

SELECT @VarcharText = [VarcharUTF8], @NVarcharText = [NVarcharUTF8]
FROM [test-sc].[dbo].[UTF8Test]
WHERE [Id] = 4
SELECT @VarcharText, Len(@VarcharText), DATALENGTH(@VarcharText)
    , @NVarcharText, Len(@NVarcharText), DATALENGTH(@NVarcharText)

 

Select Variable Lengths.png

 


Updating YuJisho: a Unicode CJK Character web dictionary

February 17, 2020

My online CJK dictionary YuJisho got a facelift again – this time, from ASP.Net MVC3 to the current MVC 5, and from Bootstrap 2 to Bootstrap 4.

I hope this gets rid of the messages in the Google Search Console 😉 :

  •  Text too small to read
  • Clickable elements too close together
  • Viewport not set

There is a little change though: In times of GDPR &co, queries to Wikipedias and Wiktionaries need to invoked by clicking the “Query Wikipedia” button, rather than querying automatically.

click the button

click the button “Query Wikipedia”

results in links to various Wikipedias containing an article

If your browser / operating system fails to display certain Chinese characters, there is now a button “Load Glyphs” which tries to load the unsupported characters’ images as .svg from GlyphWiki.

after load glyphs

after load glyphs u

Please check the About page for more information.


SMOscript for Windows 10

February 9, 2020

I recently got notified that my tool SMOscript raises an error message when run on Windows 10.

I had a look at the problem, and indeed, as soon as the enumeration of database objects (e.g. using commands l (list) or s (script)) is to process a table, the program stop. In case of the script command, an error message is issued

Error: iterating objects: Attempt to retrieve data for object failed for Server ”.

The most recent version of SMOscript was still targeted on .Net 2.0, and used the SMO 100 assemblies. Time to upgrade! 😉

So I changed the .Net version to 4.5 and added the nuget SMO package and changed the .Net version to 4.7 to make the compiler happy.

Tests were performed successfully against SQL Server 2014 and 2019, but now SQL Server 2008 caused an error that I could work-around.

The tool now also supports options to sort database objects by schema and name, or by type and schema and name, rather than relying on the order resulting from the SMO method Database.EnumObjects().

Additionally, the -st (Sort Types) parameter allows to specify a custom sort order for database object types (rather than alphabetically), so that the parameter

smoscript -st Table,View

will process objects of types table and view first, and everything else afterwards. The parameter -st without a list of types will display the available types.

The latest version of SMOscript is available for download here.

Note that there is a regular build “V 0.40”, but also a build using the original SMO 100 assemblies.

smoscript 0.40 command-line parameters

smoscript 0.40 sort SMO Types


Database.EnumObjects() fails on SQL Server 2008

February 7, 2020

After replacing the SMO 100 assemblies (=SQL 2008) with the SMO 150 (=SQL 2019) nuget package, things worked for 2014 and 2019 databases.

But not for 2008 anymore: Calling Database.EnumObjects() now raised a FailedOperationException:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Enum objects failed for Database ‘myDatabase’.
—> Microsoft.SqlServer.Management.Sdk.Sfc.InvalidVersionEnumeratorException: Operation not supported on version 10.0.5538.
at Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadInternal()
at Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadFile(Assembly a, String strFile)
at Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitDataFromAssemblyInternal(Assembly assemblyObject, String file, ServerVersion ver, String alias, StringCollection requestedFields, Boolean store, StringCollection roAfterCreation, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition, Stream configXml)
at Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitData(String file, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.LoadElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition)
at Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetAllElements(Urn urn, ServerVersion ver, DatabaseEngineType databaseEngineType, DatabaseEngineEdition databaseEngineEdition, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetObjectsFromCache(Urn urn, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects(DatabaseObjectTypes types, SortOrder order)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects(DatabaseObjectTypes types, SortOrder order)
at Microsoft.SqlServer.Management.Smo.Database.EnumObjects()

However, iterating over the various SMO collections (Database.Tables, Database.Views, etc.) still works!

SMOscript tries to deal with this situation by handling the exception and calling the original enumeration code from back-in-the-old-days to retrieve the selected SMO objects.


Evolution of SMO DatabaseObjectTypes

February 7, 2020

The SMO library mirrors the enhancing features and functionality of SQL Server over time.

As a consequence, one can “reverse engineer” the evolution of SQL Server features by comparing the classes and enums residing in different versions Microsoft.SqlServer.Smo.dll and related assemblies.

Let’s have a look at the enumeration DatabaseObjectTypes, which is defined as

[Flags]
public enum DatabaseObjectTypes : long

in Microsoft.SqlServer.Smo.dll:

enum from to
ApplicationRole = 0x1,
ServiceBroker = 0x2,
Default = 0x4,
ExtendedStoredProcedure = 0x8,
FullTextCatalog = 0x10,
MessageType = 0x20,
PartitionFunction = 0x40,
PartitionScheme = 0x80,
DatabaseRole = 0x100,
RemoteServiceBinding = 0x200,
Rule = 0x400,
Schema = 0x800,
ServiceContract = 0x1000,
ServiceQueue = 0x2000,
ServiceRoute = 0x4000,
SqlAssembly = 0x8000,
StoredProcedure = 0x10000,
Synonym = 0x20000,
Table = 0x40000,
User = 0x80000,
UserDefinedAggregate = 0x100000,
UserDefinedDataType = 0x200000,
UserDefinedFunction = 0x400000,
UserDefinedType = 0x800000,
View = 0x1000000,
XmlSchemaCollection = 0x2000000,
SymmetricKey = 0x4000000,
Certificate = 0x8000000,
AsymmetricKey = 0x10000000,
UserDefinedTableTypes = 0x20000000,
PlanGuide = 0x40000000,
DatabaseEncryptionKey = 0x80000000,
DatabaseAuditSpecification = 0x100000000,
FullTextStopList = 0x200000000,
SearchPropertyList = 0x400000000, 110
Sequence = 0x800000000, 110

Everything stable since SMO 2005, except for SEQUENCES in SQL Server 2012 (110).

Note that the enum’s symbols match the names of the corresponding SMO classes, exception for UserDefinedTableTypes, which is represented by the class UserDefinedTableType.

SQL Server 2012 Azure (110) also introduced a feature called Federation:

enum from to
Federation = 0x1000000000, 110 130
SecurityPolicy = 0x2000000000, 130 130
ExternalDataSource = 0x4000000000, 130 130
ExternalFileFormat = 0x8000000000, 130 130
ColumnMasterKey = 0x10000000000, 130 130
ColumnEncryptionKey = 0x20000000000, 130 130
QueryStoreOptions = 0x40000000000, 130 130
DatabaseScopedCredential = 0x80000000000, 130 130

The Federation features has been dropped in SQL Server 2017 (140), causing all subsequent enumeration values to right-shift by 1 bit:

enum from to
SecurityPolicy = 0x1000000000, 140
ExternalDataSource = 0x2000000000, 140
ExternalFileFormat = 0x4000000000, 140
ColumnMasterKey = 0x8000000000, 140
ColumnEncryptionKey = 0x10000000000, 140
QueryStoreOptions = 0x20000000000, 140
DatabaseScopedCredential = 0x40000000000, 140
DatabaseScopedConfiguration = 0x80000000000, 150
ExternalLibrary = 0x100000000000, 150

Finally, there is also the version-specific value All, which is the OR’ed value of all enum values.


Which version of SQL Server and SSMS can be installed on which Windows version?

January 17, 2020

As my Windows installation ages, it collects various versions of development tools, such as SQL Server, SQL Server Management Studio (SSMS), or Visual Studio.

But for every version of Windows the question is: will the next version of product X be installable on Windows Y?

So here is the answer for SQL Server and SSMS on Windows versions 7 (32-bit), 8.1 (64-bit) and 10 (64-bit)

SQL/SSMS Version Win7-32 Win8.1-64 Win10-64
2008 10 Y ? ?
2012 11 Y Y Y
2014 12 Y Y Y
SSMS 2016 13 Y Y Y
SQL 2016 13 Y Y
SSMS 17 17 Y Y
SQL 2017 14 Y Y
SSMS 18 18 Y Y
SQL 2019 15 Y