9
XML index in SQL Server is implemented as an internal table that is a persisted version of the node table that is much the same as the XML shredding functions produce.
One of the columns in the internal table is called hid and that column contains the value used in the seek for a path expression. The value is an ordpath value. When you create a path xml ...
6
Assign the variable in the last query.
DECLARE @count INT;
WITH person_address (id)
as (
SELECT AddressID
FROM [AdventureWorks].[Person].[Address]
)
SELECT @count = count (*)
FROM person_address;
select @count;
create table t (id int);
insert into t values
(1),(1),(2),(1),(2);
declare @cnt int;
with ct as
(
select id from t
)
...
6
That data in the query plan only indicates whether the query plan requires on an ordered scan, or whether an allocation-order scan would work too. Even if the query plan does not require an ordered scan, you normally get one anyway, as allocation-order scans are only allowed under specific circumstances, as Paul White explains all here.
So since this data ...
4
If you want something other than a standard rounding, you will probably need to use a nested CASE statement.
Maybe take something like the below and tweak it:
DECLARE @Num DEC (10,2) = 8.49 --8.48
SELECT
CASE
WHEN @Num - FLOOR(@Num) >= 0.5 THEN ROUND(@Num,1)
WHEN @Num - FLOOR(@Num) = 0.49 THEN FLOOR(@Num) + 0.5
ELSE @Num
END AS NewValue
4
Assuming you have a query plan in a variable @plan you can use this:
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT IndexScan.value('concat(@Database,".",@Schema,".",@Table,".",@Index)', 'nvarchar(516)')
FROM @x.nodes('//IndexScan[@Ordered = "false"]/Object') AS n(IndexScan)...
4
You have a couple of different questions in here.
Can sp_BlitzWho get blocked?
Yes, any query can. There are DMVs that are known to ignore isolation level requests and incur blocking.
Should I use sp_BlitzWho to log wait stats by session?
I wouldn't recommend doing that on a scheduled basis, no. If you need wait stats by session, you're better off with a ...
4
Consider storing your xml in a more usual format if you can. This might require a change at an earlier stage of the process, or some pre-processing when you import the data, but it could well be worth it.
The key observation is that encoding information in element names is quite unusual. Using xml with a predictable structure (ideally conforming to a schema) ...
4
Am I wrong about this reasoning? I am new to unit testing, so perhaps I am just writing my tests wrong? Is SQL longwinded language and thus it's unit tests are longer as well?
I think this is a great (though somewhat opinion-based) question. The past few years I've attempted to implement tSQLt unit testing in a personal OSS project (https://dba-multitool....
4
The best option is to have a separate Dev/Test server where you can have a full instance of your Data Warehouse. If you don't have a seperate server, you might use an Azure Virtual Machine (or similar) that can be turned off when not in use.
And you should also have a local dev environment, probably with a subset of the data. This allows teams to work in ...
answered Mar 21 at 15:38
David Browne - Microsoft
31.8k2 gold badges32 silver badges70 bronze badges
3
My question is how can I check what in TempDB if it is would have
caused this spike?
To analyze what impact those queries have on tempdb you could use sp_whoisactive since one of the info it outputs is the use of tempdb:
The most confusing of these columns are those related to tempdb. Each of the columns reports a number of 8 KB pages. The
[...
3
Unless you're on a very old version of SQL Server, the LEAD function should work.
SELECT
MyDate AS CurrentDate,
LEAD(MyDate, 1, NULL) OVER
(
ORDER BY
MyDate
) AS NextDate
FROM #Dates
ORDER BY CurrentDate;
3
A space is not a valid range delimiter so LIKE [ -~] will not work. That becomes a test for those three characters only.
You could check the ASCII code directly, rather than using LIKE:
DECLARE @input VARCHAR(20)
SET @input = 'text'
DECLARE @index INT
SET @index = 1
DECLARE @output VARCHAR(32)
SET @output = ''
WHILE ASCII(SUBSTRING(@input, @index, 1)) ...
3
You should have an extended event session in place but as a quick and dirty solution you can use the report below:
2
Use DDL trigger to capture events such as "table change" or "stored procedure change"
See here an example: https://www.sqlshack.com/database-level-ddl-trigger-over-the-table/
https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15
It does not matter if your database is in simple, or full ...
2
As per my comment, the actual querying of the tables to check which nullable columns are completely NULL is the more difficult part to this problem.
To get you started you can use Dynamic SQL and the system DMVs to build the queries you'll need like this:
-- Temp table to store the results for later
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (...
2
Directly plagiarized from my answer on a similar question:
If you're not already logging changes yourself in your user defined tables (either with a column to denote when a change occurs, at least, or by capturing the actual changes themselves), then you'll need to implement a feature that does so for your. Here is a list of features you can use to ...
1
I believe your CTE already gives you the list of all distances between any two Cities, so you just need to join the CTE to itself on reversed City fields to get the reverse results like so:
WITH CityDistances AS
(
SELECT
CITYA_id,
CITYA,
CITYB_id,
CityB,
DIS_IN_KM
FROM DB.dbo.DISTANCE d
INNER JOIN cities ...
1
I don't have an environment to test with, but the below might work for you.
EDIT: I see now that this is the same approach as @Verace's comment, but still, it should work in Cosmos DB.
https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery
SELECT c.userName
, c.DateTime
, c.bloodPressure
, c.temperature
FROM CheckupData c
JOIN (...
1
EVERY Column in a SELECT has to be in the GROUP BY or use a aggregator function
SELECT
c.userName,
MAX(c.DateTime),
c.bloodPressure,
c.temperature
FROM CheckupData c
GROUP BY
c.userName,c.bloodPressure, c.temperature
OR
SELECT
c.userName,
MAX(c.DateTime),
AVG(c.bloodPressure),
AVG(c.temperature)
FROM CheckupData c
...
1
If you're not already logging changes yourself in your user defined tables, then you'll need to implement a feature that does so for your. Here is a list of features you can use to accomplish this:
Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.
Temporal Tables - System versioned ...
1
So a couple things:
You are collating both sides of the predicate comparison which can affect your query's cardinality estimate which can severely affect performance. It looks like you're doing this to ensure a case insensitive comparison. You're better off storing the data in tables or a database with that collation specified instead, so you can eliminate ...
1
Unit testing is not a simple thing. If an application will need maintenance, it will benefit the quality of the product by filtering out most errors before being handed over to the end user. The advantage of automatic testing, and testing tout court, is very often ignored.
"In context of "normal" programming languages I've heard the advice ...
1
The first query is the fastest, with an iNDEX on order_number
CREATE TABLE mytable (col1 int, col2 int, order_number varCHAR(50))
GO
SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value1'
UNION
SELECT col1, col2
frOM mytable
WHERE order_number = 'unique_value2'
GO
col1 | col2
---: | ---:
SELECT col1, col2
frOM mytable
WHERE order_number = '...
1
I would keep the clustered index in-place especially if the data is being inserted in large lumps (rather than lots of individual inserts).
You should drop the non-clustered indexes if rebuilding the data from scratch.
NOTE: As per your mention of truncating, this answer is talking about rebuilding a table from scratch.
Considerations would be different if ...
1
There are two main issues which cause implicit (or explicit) conversion to make a big difference to the query plan:
The main point of problems is where a join or filter predicate, or an ordering or grouping, is over a converted column. Note also that this applies equally to any function on a column used in such a context. A column which is just selected has ...
Only top voted, non community-wiki answers of a minimum length are eligible
