Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-data-types below:

Table data types in Synapse SQL - Azure Synapse Analytics

In this article, you'll find recommendations for defining table data types in Synapse SQL Dedicated Pool.

Synapse SQL Dedicated Pool supports the most commonly used data types. For a list of the supported data types, see data types in the CREATE TABLE statement. For Synapse SQL Serverless, refer to article Query storage files with serverless SQL pool in Azure Synapse Analytics and How to use OPENROWSET using serverless SQL pool in Azure Synapse Analytics

Minimizing the size of data types shortens the row length, which leads to better query performance. Use the smallest data type that works for your data.

• Avoid defining character columns with a large default length. For example, if the longest value is 25 characters, then define your column as VARCHAR(25).
• Avoid using NVARCHAR when you only need VARCHAR.
• When possible, use NVARCHAR(4000) or VARCHAR(8000) instead of NVARCHAR(MAX) or VARCHAR(MAX).
• Avoid using floats and decimals with 0 (zero) scale. These should be TINYINT, SMALLINT, INT, or BIGINT.

Note

If you are using PolyBase external tables to load your Synapse SQL tables, the defined length of the table row cannot exceed 1 MB. When a row with variable-length data exceeds 1 MB, you can load the row with BCP, but not with PolyBase.

If you're migrating your database from another SQL database, you might encounter data types that aren't supported in Synapse SQL. Use this query to discover unsupported data types in your existing SQL schema.

SELECT  t.[name], c.[name], c.[system_type_id], c.[user_type_id], y.[is_user_defined], y.[name]
FROM sys.tables  t
JOIN sys.columns c on t.[object_id]    = c.[object_id]
JOIN sys.types   y on c.[user_type_id] = y.[user_type_id]
WHERE y.[name] IN ('geography','geometry','hierarchyid','image','text','ntext','sql_variant','xml')
 OR  y.[is_user_defined] = 1;

The following list shows the data types that Synapse SQL doesn't support and gives alternatives that you can use instead of the unsupported data types.

For more information on developing tables, see the development overview.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4