New answers tagged t-sql
0
You cannot use dynamic SQL here, as that will not work inside a TVF. You could use dynamic to generate the actual code below though.
Given that you are on SQL Server 2016, you do not have STRING_AGG available, so you are going to have to use FOR XML/STUFF method, which is pretty complex with multiple columns.
It's not necessary or performant to keep querying ...
1
is there a way to reference the columns by ordinal position
Yes there is but I'm not sure how that would help you doing what you want. You put the ordinal position in the predicate, as you already do for row[1].
Changing '/row[1]/@name' to instead get the third column would look like '/row[1]/@*[3]'. You should be aware of that null values does not create ...
1
There is something you need to keep in mind for windowing functions: they are performed on every row. Thus windowing functions like lag and lead apply to the previous and next rows. By adding an order by, you are basically saying “in this grouping, up to and including the current row, what is the max value for column item_name”.
This doesn’t make sense ...
2
What about this?
-- TSQL to find Databases, corresponding elastic pool names and DB edition
SELECT
@@SERVERNAME as [ServerName],
dso.elastic_pool_name,
d.name as DatabaseName,
dso.edition
FROM
sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
...
0
Found it, though the solution is rather limited, because this DMV is only available in the master database and you can't actually refer to it from within a user database.
select d.name, o.*
from sys.database_service_objectives o
join sys.databases d on
o.database_id = d.database_id
3
The windowing clause you use (in this case the default of "range between unbounded preceding and current row") operates on what you order by.
There is some logic to that: to be able to know what is "preceding" or "following" you have to talk about ordered data.
In query-1 you order by item_index, which is also what you partition ...
1
Many DDL statements must either start a batch or be the only statement in a batch. So the general solution is to use dynamic SQL. EG
I want to use CREATE FUNCTION in a block that will "swallow" errors, but this
BEGIN TRY
exec('
CREATE FUNCTION test (@ID int)
RETURNS int
AS
BEGIN
RETURN(2 * @ID)
END
')
END TRY
BEGIN CATCH
END CATCH
0
this query will search in contracts_view and check if the new contract start date between any other active contracts:
declare @d as date = '2025-04-22'
IF EXISTS (SELECT * FROM contracts_view where contracts_view.cust_id=123456789 and @d >= contracts_view.date_start and @d <= contracts_view.date_end
and contracts_view.shop_id = (SELECT STRING_AGG(...
5
The error message refers to the elastic pool so the problem might relate to another database than the current one. You can retrieve the information about the elastic pool using the following DMV query vs the Master database
SELECT TOP 5 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB, *
FROM sys....
3
max(t.Item_name)over(partition by t.item_index order by item_index) new_column
Let's take a group where t.item_index = 0. It is
Item_index
Item_name
0
A
0
C
0
E
When order by item_index is applied then all rows have the same value, hence all of them are included into the frame, and all rows values are used for MAX() selection. So the value 'E' is ...
5
The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section:
ORDER BY
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
If it is specified, and a ROWS/RANGE is not ...
4
GREATEST and LEAST are now supported in Azure SQL, and will be supported in the next version of SQL Server after SQL Server 2019. (SQL Server 2021?)
2
You need to add another column so that the aggregate function you have for the pivot operation has something to bite into. Try the below.
DROP TABLE IF EXISTS #imgText
create table #imgText (
rowindex int,
imgName varchar(50)
)
insert into #imgText (rowindex,imgName) values (0,'dog')
insert into #imgText (rowindex,imgName) values (1,'plant')
insert into #...
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 (...
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
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
...
0
That is exactly what triggers do.
Please have a look at https://www.sqlservertutorial.net/sql-server-triggers/ or https://www.sqlshack.com/triggers-in-sql-server/ or https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/ you will find more information.
If you just need an update trigger it will be something like:
create trigger triggername ...
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 ...
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)...
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 ...
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
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
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) ...
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 ...
0
This refers to the role public.
every user gets this role, and so the rights that the role public has, are valid for all users, so there is no singular username shown in the result only the text `{All Users}´
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 ...
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 ...
0
Let's take as an example this query that will run for 60 seconds:
DECLARE @i INT = 1;
WHILE (@i <= 60)
BEGIN
WAITFOR DELAY '00:00:01'
print FORMAT(GETDATE(),'hh:mm:ss')
SET @i = @i + 1;
END
We want to stop it if the query runs for more than 10 seconds.
So we create an agent job and as step we set this ...
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....
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 ...
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 (...
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)) ...
-1
There is undocumented sp_MSforeachdb procedure:
DECLARE @TSQL nvarchar(2000)
SET @TSQL = '
IF ''?'' like ''mydbset-%''
BEGIN
USE [?];
PRINT ''********** database: [?] ************''
select count(*) from information_schema.tables; -- ANY SQL or SP Call in database
END
'
-- Executing TSQL for each database
EXEC sp_MSforeachdb @TSQL
0
SQL Server by itself allows the return of multiple resultsets via a stored procedure. What matters is your client calling the stored procedure and receiving the resultsets. This means you can do:
CREATE PROCEDURE p_FetchData
AS
BEGIN
SELECT col1, col2
from table1
WHERE order_number = 'unique_value1';
SELECT col1, col2
from table2
WHERE order_number = '...
0
An alternative to nbk's solution is using the UNION ALL clause like so:
SELECT col1, col2
WHERE order_number = 'unique_value1'
UNION ALL
SELECT col1, col2
WHERE order_number = 'unique_value2'
If you do add an index to the order_number field, you can test which query is faster in your case (either using the OR clause or UNION ALL clause), it just depends ...
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 ...
Top 50 recent answers are included
