@Criggie Yeah, I always thought that banner was odd. Still, that's what it says. Nothing about an exemption for legitimate user questions or the information being available elsewhere. Maybe it's historical, idk.
@ErikDarling Well, besides that there are just so many gotchas. They're easy to add in at the start but will bite you in increasingly subtle ways later on. Witness the number of questions about matching up identity values when loading data into tables in a structural relationship.
Never mind the remote server issues everyone is so well-versed on
Not with a checked foreign key relationship, no. Nested DML doesn't work when the table is on either side of an enforced FK relationship. Note also that only an outer INSERT is permitted.
Temporarily not checking the FK would almost work, but the identity on the child table presents another roadb...
There are patterns to work around most things in most cases, but they're not universal and represent surprise learning for most people new to SQL Server
At some point we decided it was acceptable to use MERGE and OUTPUT into a table variable to map identity 'keys'.
DECLARE @T table (c1 integer PRIMARY KEY, c2 char(4) NULL);
DECLARE @A table (j nvarchar(max) NOT NULL);
INSERT @T (c1, c2) VALUES (1, 'abcd');
DELETE T
OUTPUT CA.j INTO @A
FROM @T AS T
CROSS APPLY
(
SELECT T.*
FOR JSON
PATH,
INCLUDE_NULL_VALUES,
WITHOUT_ARRAY_WRAPPER
) AS CA (j)
WHERE
c1 = 1;
SELECT *
FROM @A AS A;
> Note that as of SQL Server 2008 the results of the outer operation must be an INSERT-SELECT. That’s a rather frustrating limitation, and I really hope the SQL Server team loosens the restrictions in the next version of the product.
> A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.
> Remote table-valued function calls are not allowed.
> The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables.
> Remote tables are not supported in MATCH clauses.
> Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.
Isn't communism more like: take from the peasants (workers) and distribute unevenly between family (30%), friends (30%), politicians (30%) and the citizens (10%)?
> In 8.0, we changed the default translation for parameterized lists from constantization to parameterization (e.g. with OPENJSON); this is better for plan/query caching (single SQL), but can produce bad query plans (#32394). For 9, we're adding extensive means to control the parameterization vs. constantization behavior, both at the global context options level (#34344) and at the per-collection level (EF.Constant, EF.Parameter (#34345)).
For 10, we plan to reexamine what the default should be. The problem with the current full parameterization, is that the bad plans it occasionally cause…
> In addition, the main problem with constantization is caused by SQL Server's behavior to cache query plans on the very first execution. Other databases do not typically do this...
It must be difficult to try to support every known database
it's quite irksome that i still need to increment in this case
DECLARE
@i integer = 1,
@e integer =
(
SELECT
MAX(d.database_id)
FROM sys.databases AS d
),
@s nvarchar(MAX) = N'';
SET @s = N'
SELECT TOP (1)
@i = d.database_id
FROM sys.databases AS d
WHERE d.database_id >= @i
ORDER BY
d.database_id;';
WHILE
@i <= @e
BEGIN
EXEC sys.sp_executesql
@s,
N'@i integer OUTPUT',
@i OUTPUT;
RAISERROR(N'%i of %i', 0, 1, @i, @e) WITH NOWAIT;
SELECT
@i += 1
END;
oh wait nevermind
DECLARE
@i integer = 0,
@e integer =
(
SELECT
MAX(d.database_id)
FROM sys.databases AS d
),
@s nvarchar(MAX) = N'';
SET @s = N'
SELECT TOP (1)
@i = d.database_id
FROM sys.databases AS d
WHERE d.database_id > @i
ORDER BY
d.database_id;';
WHILE
@i < @e
BEGIN
EXEC sys.sp_executesql
@s,
N'@i integer OUTPUT',
@i OUTPUT;
RAISERROR(N'%i of %i', 0, 1, @i, @e) WITH NOWAIT;
END;
DECLARE
@i integer = 0,
@c CURSOR;
SET @c = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT d.database_id
FROM sys.databases AS d
ORDER BY d.database_id;
OPEN @c;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM @c INTO @i;
IF @@FETCH_STATUS = -1 BREAK;
RAISERROR(N'%i of %i', 0, 1, @i, @@CURSOR_ROWS) WITH NOWAIT;
END;
> In this query, SQL Server believes that LOTS of users are going to match, so it decides to just go start fetching rows from the Posts table across the network.
WITH
Comments AS
(
SELECT
n = ROW_NUMBER() OVER
(
ORDER BY
c.CreationDate
)
FROM dbo.Comments AS c
)
SELECT
c.*
FROM Comments AS c
WHERE c.n = 0
OPTION
(
USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')
);
> Currently, SQL Server isn’t able to pull the data ordered from a B-tree index with a parallel scan and distribute it gradually and dynamically over worker threads like what would be optimal for the Window Aggregate operator without a mediator like a batch Sort operator.
> Doing it without a mediator would require more work on Microsoft’s part in the SQL Server engine, and hopefully we will see such support in the future. For now, the optimizer has to make a choice between a serial plan without a Sort operator and a parallel plan with one.
