Skip to content

Commit a5836b1

Browse files
CarlRabelerCarlRabeler
committed
Merge branch 'master' of https://github.com/MicrosoftDocs/sql-docs-pr into 20190307-joe
2 parents e48516d + 4042926 commit a5836b1

3 files changed

Lines changed: 48 additions & 38 deletions

File tree

‎docs/relational-databases/performance/intelligent-query-processing.md‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -28,16 +28,16 @@ ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;
2828

2929
The following table details all intelligent query processing features, along with any requirement they have for database compatibility level.
3030

31-
| **IQP Feature** | **Supported in Azure SQL Database** | **Supported in SQL Server** |
32-
| --- | --- | --- |
33-
| [Adaptive Joins (Batch Mode)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-adaptive-joins) | Yes, under compatibility level 140| Yes, starting in SQL Server 2017 under compatibility level 140|
34-
| [Approximate Count Distinct](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#approximate-query-processing) | Yes, public preview| Yes, starting in SQL Server 2019 CTP 2.0, public preview|
35-
| [Batch Mode on Rowstore](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-on-rowstore) | Yes, under compatibility level 150, public preview| Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview|
36-
| [Interleaved Execution](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#interleaved-execution-for-mstvfs) | Yes, under compatibility level 140| Yes, starting in SQL Server 2017 under compatibility level 140|
37-
| [Memory Grant Feedback (Batch Mode)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback) | Yes, under compatibility level 140| Yes, starting in SQL Server 2017 under compatibility level 140|
38-
| [Memory Grant Feedback (Row Mode)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#row-mode-memory-grant-feedback) | Yes, under compatibility level 150, public preview| Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview|
39-
| [Scalar UDF Inlining](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#scalar-udf-inlining) | No | Yes, starting in SQL Server 2019 CTP 2.1 under compatibility level 150, public preview|
40-
| [Table Variable Deferred Compilation](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#table-variable-deferred-compilation) | Yes, under compatibility level 150, public preview| Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview|
31+
| **IQP Feature** | **Supported in Azure SQL Database** | **Supported in SQL Server** |**Description** |
32+
| --- | --- | --- |--- |
33+
| [Adaptive Joins (Batch Mode)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-adaptive-joins) | Yes, under compatibility level 140| Yes, starting in SQL Server 2017 under compatibility level 140|Adaptive joins dynamically select a join type during runtime based on actual input rows.|
34+
| [Approximate Count Distinct](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#approximate-query-processing) | Yes, public preview| Yes, starting in SQL Server 2019 CTP 2.0, public preview|Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint. |
35+
| [Batch Mode on Rowstore](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-on-rowstore) | Yes, under compatibility level 150, public preview| Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview|Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes. |
36+
| [Interleaved Execution](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#interleaved-execution-for-mstvfs) | Yes, under compatibility level 140| Yes, starting in SQL Server 2017 under compatibility level 140|Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.|
37+
| [Memory Grant Feedback (Batch Mode)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback) | Yes, under compatibility level 140| Yes, starting in SQL Server 2017 under compatibility level 140|If a batch mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% memory, reduce the memory grant side for consecutive executions.|
38+
| [Memory Grant Feedback (Row Mode)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#row-mode-memory-grant-feedback) | Yes, under compatibility level 150, public preview| Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview|If a row mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% memory, reduce the memory grant side for consecutive executions.|
39+
| [Scalar UDF Inlining](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#scalar-udf-inlining) | No | Yes, starting in SQL Server 2019 CTP 2.1 under compatibility level 150, public preview|Scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains.|
40+
| [Table Variable Deferred Compilation](https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017#table-variable-deferred-compilation) | Yes, under compatibility level 150, public preview| Yes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview|Use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.|
4141

4242
## Batch mode adaptive joins
4343

‎docs/t-sql/functions/collation-functions-collationproperty-transact-sql.md‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,7 @@ monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-s
2424
# Collation Functions - COLLATIONPROPERTY (Transact-SQL)
2525
[!INCLUDE[tsql-appliesto-ss2008-all-md](../../includes/tsql-appliesto-ss2008-all-md.md)]
2626

27-
This function returns the property of a specified collation in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].
27+
This function returns the requested property of a specified collation.
2828

2929
![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)
3030

@@ -43,10 +43,10 @@ The collation property. The *property* argument has a **varchar(128)** data type
4343

4444
|Property name|Description|
4545
|---|---|
46-
|**CodePage**|Non-Unicode code page of the collation. See [Appendix G DBCS/Unicode Mapping Tables](https://msdn.microsoft.com/library/cc194886.aspx) and [Appendix H Code Pages](https://msdn.microsoft.com/library/cc195051.aspx) to translate these values, and to see their character mappings.|
47-
|**LCID**|Windows LCID of the collation. See [LCID Structure](https://msdn.microsoft.com/library/cc233968.aspx) to translate these values (you will first need to convert to **varbinary**).|
48-
|**ComparisonStyle**|Windows comparison style of the collation. Returns 0 for all binary collations - both (\_BIN) and (\_BIN2) - as well as when all properties are case sensitive. Bitmask values:<br /><br /> Ignore case : 1<br /><br /> Ignore accent : 2<br /><br /> Ignore Kana : 65536<br /><br /> Ignore width : 131072<br /><br /> Note: the variation-selector-sensitive (\_VSS) option is not represented in this value, even though it affects the comparison behavior.|
49-
|**Version**|The version of the collation, derived from the collation ID version field. Returns an integer value between 0 and 3.<br /><br /> Collations with "140" in the name return 3.<br /><br /> Collations with "100" in the name return 2.<br /><br /> Collations with "90" in the name return 1.<br /><br /> All other collations return 0.|
46+
|**CodePage**|Non-Unicode code page of the collation. This is the character set used for **varchar** data. See [Appendix G DBCS/Unicode Mapping Tables](https://msdn.microsoft.com/library/cc194886.aspx) and [Appendix H Code Pages](https://msdn.microsoft.com/library/cc195051.aspx) to translate these values, and to see their character mappings.<br /><br />Base data type: **int**|
47+
|**LCID**|Windows locale ID of the collation. This is the culture used for sorting and comparison rules. See [LCID Structure](https://msdn.microsoft.com/library/cc233968.aspx) to translate these values (you will first need to convert to **varbinary**).<br /><br />Base data type: **int**|
48+
|**ComparisonStyle**|Windows comparison style of the collation. Returns 0 for binary collations - both (\_BIN) and (\_BIN2) - as well as when all properties are sensitive - (\_CS\_AS\_KS\_WS) and (\_CS\_AS\_KS\_WS\_SC) and (\_CS\_AS\_KS\_WS\_VSS). Bitmask values:<br /><br /> Ignore case : 1<br /><br /> Ignore accent : 2<br /><br /> Ignore Kana : 65536<br /><br /> Ignore width : 131072<br /><br /> Note: the variation-selector-sensitive (\_VSS) option is not represented in this value, even though it affects the comparison behavior.<br /><br />Base data type: **int**|
49+
|**Version**|The version of the collation. Returns a value between 0 and 3.<br /><br /> Collations with "140" in the name return 3.<br /><br /> Collations with "100" in the name return 2.<br /><br /> Collations with "90" in the name return 1.<br /><br /> All other collations return 0.<br /><br />Base data type: **tinyint**|
5050

5151
## Return types
5252
**sql_variant**

‎docs/t-sql/statements/windows-collation-name-transact-sql.md‎

Lines changed: 33 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "Windows Collation Name (Transact-SQL) | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "02/21/2019"
4+
ms.date: "03/06/2019"
55
ms.prod: sql
66
ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw"
77
ms.reviewer: ""
@@ -36,7 +36,7 @@ Specifies the Windows collation name in the COLLATE clause in [!INCLUDE[ssNoVers
3636
CollationDesignator_<ComparisonStyle>
3737
3838
<ComparisonStyle> :: =
39-
{ CaseSensitivity_AccentSensitivity [ _KanatypeSensitive ] [ _WidthSensitive ]
39+
{ CaseSensitivity_AccentSensitivity [ _KanatypeSensitive ] [ _WidthSensitive ] [ _VariationSelectorSensitive ]
4040
}
4141
| { _BIN | _BIN2 }
4242
```
@@ -46,43 +46,53 @@ CollationDesignator_<ComparisonStyle>
4646
*CollationDesignator*
4747
Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:
4848

49-
- The sorting rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
50-
- The code page used to store non-Unicode character data.
49+
- The sorting and comparison rules that are applied when dictionary sorting is specified. Sorting rules are based on alphabet or language.
50+
- The code page used to store **varchar** data.
5151

5252
Some examples are:
5353

54-
- Latin1_General or French: both use code page 1252.
54+
- Latin1\_General or French: both use code page 1252.
5555
- Turkish: uses code page 1254.
5656

57-
*CaseSensitivity*
57+
*CaseSensitivity*
5858
**CI** specifies case-insensitive, **CS** specifies case-sensitive.
5959

60-
*AccentSensitivity*
60+
*AccentSensitivity*
6161
**AI** specifies accent-insensitive, **AS** specifies accent-sensitive.
6262

63-
*KanatypeSensitive*
63+
*KanatypeSensitive*
6464
**Omitted** specifies kanatype-insensitive, **KS** specifies kanatype-sensitive.
6565

66-
*WidthSensitivity*
66+
*WidthSensitivity*
6767
**Omitted** specifies width-insensitive, **WS** specifies width-sensitive.
6868

69-
**BIN**
69+
*VariationSelectorSensitivity*
70+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssqlv14-md.md)]
71+
72+
**Omitted** specifies variation selector-insensitive, **VSS** specifies variation selector-sensitive.
73+
74+
**BIN**
7075
Specifies the backward-compatible binary sort order to be used.
7176

72-
**BIN2**
77+
**BIN2**
7378
Specifies the binary sort order that uses code-point comparison semantics.
7479

7580
## Remarks
7681

77-
Depending on the version of the collations some code points may be undefined. For example compare:
82+
Depending on the version of the collation, some code points may not have sort weights and/or uppercase/lowercase mappings defined. For example, compare the output of the `LOWER` function when it is given the same character, but in different versions of the same collation:
7883

7984
```sql
80-
SELECT LOWER(nchar(504) COLLATE Latin1_General_CI_AS);
81-
SELECT LOWER (nchar(504) COLLATE Latin1_General_100_CI_AS);
82-
GO
85+
SELECT NCHAR(504) COLLATE Latin1_General_CI_AS AS [Uppercase],
86+
NCHAR(505) COLLATE Latin1_General_CI_AS AS [Lowercase];
87+
-- Ǹ ǹ
88+
89+
90+
SELECT LOWER(NCHAR(504) COLLATE Latin1_General_CI_AS) AS [Version80Collation],
91+
LOWER(NCHAR(504) COLLATE Latin1_General_100_CI_AS) AS [Version100Collation];
92+
-- Ǹ ǹ
8393
```
8494

85-
The first line returns an uppercase character when the collation is Latin1_General_CI_AS, because this code point is undefined in this collation.
95+
The first statement shows both uppercase and lowercase forms of this character in the older collation (collation does not affect the availability of characters when working with Unicode data). However, the second statement shows that an uppercase character is returned when the collation is Latin1\_General\_CI\_AS because this code point does not have a lowercase mapping defined in that collation.
8696

8797
When working with some languages, it can be critical to avoid the older collations. For example, this is true for Telegu.
8898

@@ -92,24 +102,24 @@ In some cases Windows collations and [!INCLUDE[ssNoVersion](../../includes/ssnov
92102

93103
The following are some examples of Windows collation names:
94104

95-
- **Latin1_General_100_**
105+
- **Latin1\_General\_100\_CI\_AS**
96106

97-
Collation uses the Latin1 General dictionary sorting rules, code page 1252. Is case-insensitive and accent-sensitive. Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252. Shows the version number of the collation if it is a Windows collation: _90 or _100. Is case-insensitive (CI), and accent-sensitive (AS).
107+
Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252. It is a version \_100 collation, and is case-insensitive (CI) and accent-sensitive (AS).
98108

99-
- **Estonian_CS_AS**
109+
- **Estonian\_CS\_AS**
100110

101-
Collation uses the Estonian dictionary sorting rules, code page 1257. Is case-sensitive and accent-sensitive.
111+
Collation uses the Estonian dictionary sorting rules and maps to code page 1257. It is a version \_80 collation (implied by no version number in the name), and is case-sensitive (CS) and accent-sensitive (AS).
102112

103-
- **Latin1_General_BIN**
113+
- **Japanese\_Bushu\_Kakusu\_140\_BIN2**
104114

105-
Collation uses code page 1252 and binary sorting rules. The Latin1 General dictionary sorting rules are ignored.
115+
Collation uses binary code point sorting rules and maps to code page 932. It is a version \_140 collation, and the Japanese Bushu Kakusu dictionary sorting rules are ignored.
106116

107117
## Windows Collations
108118

109119
To list the Windows collations supported by your instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], execute the following query.
110120

111121
```sql
112-
SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%';
122+
SELECT * FROM sys.fn_helpcollations() WHERE [name] NOT LIKE N'SQL%';
113123
```
114124

115125
The following table lists all Windows collations supported in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)].

0 commit comments

Comments
 (0)