New answers tagged t-sql
2
You need to declare a table variable with the same shape as the TVF result, then use INSERT INTO not SELECT INTO, and remember to add brackets/parentheses to the end of the function name when invoking it, i.e. Foo() not just Foo:
DECLARE @Table TABLE (
[Id] [int] NULL,
[Number] [int] NOT NULL,
[Name] [nvarchar](255) NULL,
... dozens of ...
10
Probably you're moving the row when you set Deleted=1 and reading it again with your FAST_FORWARD cursor. Use a STATIC cursor instead, which will iterate a copy of the data, and avoid mutating the data structure you are traversing.
DECLARE st CURSOR LOCAL STATIC FOR . . .
8
You want WHILE @@FETCH_STATUS = 0 which means continue unless something isn't right.
Using <> -1 means it will continue even if the row fetched was missing, or it's not performing a fetch operation, making it infinite unless you get -1 as a return value, since there are 4 return values for @@FETCH_STATUS.
0 The FETCH statement was successful.
-1 ...
0
Thnaks, it has helped a lot. There is a small bug as the Duration is not in Seconds it is in HHMMSS
I have fix it this way:
SELECT x.executable_name
, x.start_time
, x.end_time AS end_time
, datediff(minute, x.start_time, x.end_time) AS Duration
FROM (
SELECT DISTINCT --TOP 100 PERCENT
cast([sJOB].[job_id] AS VARCHAR(max)) AS execution_id
, [sJSTP]...
4
Here's a timeline of the events you described, as well as my commentary on what was the likely cause of your bad plan.
You're alerted of a slow query that was previously fast
Bad times, I empathize!
You go crazy and run "the forbidden dbcc freeproccache" and it doesn't help
Usually, clearing the cache fixes problems like this if the problem is parameter ...
5
This can be due to the maximum amount of characters that the result to grid can return, 65535.
Starting from SSMS 18.2 you are able to change this.
Thanks Aaron Bertrand for pointing that out.
Aaron also mentions:
if 64kb is the limit you’re hitting, there are a lot of places where
statement text is truncated long before that any way (see literally
...
0
The UpdateStatistics portion of the index and maintenance solution is set to NULL by default.
2
Why not both? A stale index with bad statistics can cause a bad plan to be generated.
0
You can merge the data spread across the three columns by using a CASE switch.
I have an example to share but, without detail about the data you are storing in the Employed, Non-Employed, and Retired columns I am guessing at a few things. If you can update your question with more detail about the type of data store in those columns then it will help guide ...
-3
Use JSON or XML for the properties
0
As pointed out by @SQLRaptor in a comment, you're running this code against an empty table. In fact, it's a table variable, which can only contain rows while the code is running.
The following example shows that your code works if rows are inserted into the table:
DECLARE @Table TABLE (Col1 int, Col2 datetime);
INSERT INTO @Table (Col1, Col2)
VALUES (1, '...
1
For what it's worth, this behaviour changes in SQL Server 2016. When I try against 2017 with COMPATIBILITY_LEVEL = 120 I see your observed behaviour. When COMPATIBILITY_LEVEL = 130 both input values produce the same rounded output value 9.77.
There is some documentation here but it is not conclusive. I do not have 2014 documentation to hand but it would be ...
0
The best solution would be to ditch the RR (or RRRR) notation completely. It was meant as a short-term, kick the can down the road, fix to buy some time remediating for Y2k. That was TWENTY YEARS ago. Long, long past time to wean ourselves it and simply start using 4-digit years in everything we do.
3
Randi's answer works perfectly, and Akina's is much tidier if you are on SQL Server 2017 or better, but on older versions you can do this with slightly less code and fewer operations like reverse/stuff. Given this data:
CREATE TABLE #temp(col varchar(255));
INSERT #temp(col)
SELECT 'foo' UNION ALL SELECT '"bar"'
UNION ALL SELECT 'blat"' UNION ALL ...
4
SELECT TRIM(' "' FROM fieldname) fieldname
FROM tablename
TRIM() function requires SQL Serer 2017.
5
You could try something like this
SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, ''))
WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, ''))
WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '')
ELSE fieldname END as ...
2
My question:
Is there a way to express RR in T-SQL?
It can be done, for example, as
-- from date to string
SELECT FORMAT(datetime_field, CASE WHEN YEAR(datetimefield) > 49
THEN 'yyyy-MM-dd'
ELSE 'yy-MM-dd'
END) AS date_formatted_as_RR, ...
--...
0
if ID and ID2 columns are key columns then you need to worry about that faulty Update Statement.
Agree, those values aren't being changed but it create very expensive Query Plan.
In Fact you should Compare the query Plan with and without mt.col1 = s.col1
, mt.col2 = s.col2 in Update your Self.
Begin Tran
UPDATE mt
SET mt.col1 = s.col1
, ...
1
SQL server may choose to perform an in-place update or an insert and a delete depending on many factors and you shouldn’t worry about it.
Since the keys are not being updated I would leave them out of the SET clause to help the optimizer make the best decision.
If you are using SQL Server 2008 or later, look at the MERGE statement which can replace both ...
4
From error message,
Arithmetic overflow error converting varchar to data type numeric.
My first place where I could change is :
ELSE a.Marks
and change it to
ELSE try_convert(int, a.Marks)
or convert to decimal , if you want
Later edit:
In case expression you have a few THEN with different data types: int, numeric, varchar. Due to data type ...
0
Instead of processing DataGatheringRuntime table which is quite big at ~100GB) mutiples times
process it single time by putting them in #temp table or CTE
Then remove Distinct. If there is duplicate data then find the reason behind duplicate data
and remove duplicate data by writing correct query.
What is the purpose of Distinct and UNION in same query ?
...
1
WITH cte AS ( SELECT PolicyID,
PolicylocationID,
Country,
COUNT(CASE WHEN Country = 'US' THEN 1 END) OVER (PARTITION BY PolicyID) us,
COUNT(Country) OVER (PARTITION BY PolicyID) total
FROM test
)
SELECT PolicyID,
PolicylocationID,
...
1
If it were me?
Nuke it from orbit and restore from backup.
Stop any processes that rely on this database and restore from your most recent backups.
After you're stable, you can go through the academic exercise of trying to repair as much of the corrupt media as you've got the energy & budget to put up with. Assuming you have a strong understanding ...
2
Check out the INFORMATION_SCHEMA. I guess something like:
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ...
AND TABLE_NAME = ...
AND DATA_TYPE IN (...)
0
For an arbitrary number of columns into a single row, you would have to build the dynamic SQL yourself. There may be cleaner ways than this, but the below is a start point.
Note that, unless the query optimiser can do something nice for you (which I doubt), it will scan the original data a huge number of times (at least 2 + colcount * rowcount):
DECLARE ...
2
In case an update can affect multiple rows, you have to protect against two things:
We want to consider updates that swap values between similar rows. If there are two John Smiths who need their JobCodes updated (first John from 1 to 2; second John from 2 to 1), we need to be careful to say they both were updated.
We only want to log the changed rows in ...
1
Avoid <> in where condition.Instead use >-1
When you are already filtering anything like where s.cfcifno<>-1 ,then no need of putting same in case condition because that condition is already filtered.
Your query seem ok to me,
SELECT
S.OPRDATE ,
S.CFCIFNO ,
SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 ...
2
You can try this.
@Stringss is a variable to simulate the field you already obtained
DECLARE @String NVARCHAR(MAX)='S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'
SELECT REVERSE(SUBSTRING(REVERSE(@String),0,CHARINDEX('\',REVERSE(@String),0)))
0
I've just been interested in this as well.
AFAICT using sys.dm_tran_current_transaction you can get the id of the current transaction.
Then with sys.dm_tran_active_transactions you can get all transactions with the transactions_begin_time
1
Hope this script includes all you are trying to retrieve.
This script retrieving CHECK and DEFAULT constraints. You can use INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE to retrieve PK & FK columns.
DECLARE @Constraint TABLE (
[Database] [nvarchar](128) NULL,
[Schema Name] [nvarchar](64) NULL,
[Table Name] [sysname] NOT NULL,
...
2
It's kind of a barebone question, but...
@DateToUse is the date you are searching for.
@StringOutput is the cto column.
I'm using the ID field as a tie-breaker in order to protect against duplicate or overlapping ranges in from/to fields.
DECLARE @DateToUse DATE
DECLARE @StringOutput VARCHAR(50)
SET @DateToUse = '3/2/2019'
SET @StringOutput = (...
1
what about doing one of these:
DENY SELECT ON SCHEMA::DBO TO PUBLIC (public or a login or set of logins)
ALTER ROLE [db_denydatareader] ADD MEMBER [your login]
Both worked for me and the complete test is below:
use master
go
--create a login for testing
CREATE LOGIN Radhe WITH PASSWORD='HareKrishna001!', DEFAULT_DATABASE=master;
GO
--create a database ...
2
You don't need dynamic SQL for that. Something like this should work:
WHERE date_column >= DATEFROMPARTS(
YEAR(GETDATE()) - CASE WHEN MONTH(GETDATE()) >= 8 THEN 0 ELSE 1 END,
8, 1 )
2
Try using CASE expressions as you can specify qualifiers like what you're describing.
something like...
Code :
Select Count (*)
CASE when date >= 1st August year X THEN enteredondate = currentyear/08/01
ELSE enteredondate= previousyear/08/01
End
from DateTable
2
you can try
EXEC sp_MSforeachtable @command1 = 'PRINT (''REVOKE SELECT on ? TO [Role] AS [dbo]'')'
and instead of print
EXEC sp_MSforeachtable @command1 = 'REVOKE SELECT on ? TO [Role] AS [dbo]'
1
The Cursor approach:
DECLARE @statement NVARCHAR(100) -- statement
DECLARE @table SYSNAME -- Table name
DECLARE db_cursor CURSOR FOR
SELECT t.name
FROM sys.tables t
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
WHERE s.name = 'dbo' -- or just test for schema_id = 1
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @table
WHILE @@FETCH_STATUS =...
Top 50 recent answers are included
