Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,537
questions
1
vote
0answers
30 views
How to select all files of a SQL Server database from blob folder?
I have a SQL Server database.
You can use the following script to create similar database:
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'test', FILENAME = N'C:\Program Files\...
0
votes
0answers
13 views
Clustered Index in Big Partitioned Table on SQL Server 2019
We are in process of developing a project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose "Always-On" solution which will contain:
One primary ...
0
votes
0answers
39 views
T SQL/ Reporting Question
I have a view that is pulling sales from multiple companies and I need to basically 'combine' two rows so they display as one. Here is a picture of what the data looks like:
I need to take all rows ...
0
votes
1answer
39 views
Architecture of table for lot of inserts per second in SQL Server 2019
We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution which will contain:
One Primary ...
0
votes
1answer
13 views
SQL Server 2000 Database Monitoring
I have never worked on SQL Server 2000 and I got a requirement to check capture the following information to one particular database.
DBName CurrentDate SPID BatchDuration ApplicationName ...
0
votes
0answers
20 views
Pivot table and summarizing 2 columns
so, I have a pivot table in which I have orders and they're grouped by year, I want to also count all the orders by year. I'm having a hard time doing this. this is the query:
DECLARE @fromDate AS ...
0
votes
3answers
58 views
Discarding results from CTE based on another CTE
WITH R1 AS
(
// some complex query
),
R2 AS
(
// uses R1
// another complex query
),
// here I already have dataset with Id, Name, ProductReference and other columns
R3 AS
(
select ...
3
votes
1answer
66 views
How can SUSER_SNAME return the localized user name?
I need to assign network service user to SQL Server database programmatically, so I am running this script:
USE [MyDB]
GO
DECLARE @user nvarchar(50)
DECLARE @SQLStatement nvarchar(500)
SET @user = ...
2
votes
2answers
64 views
Query list of logins that have logged in the last X days on SQL Server
Is there any good query that included internal system objects like sys.dm_exec_sessions or sys.dm_exec_connections that would list logins that have connected to the SQL server over the last X days? 15,...
1
vote
3answers
53 views
Carry forward non-null value with a windowing function? [duplicate]
In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have:
name ...
0
votes
0answers
43 views
Within a given database: what objects cant have the same name?
In a given database what objects (Table, View, Index, Constraint, Trigger etc) can or can't have the same name?
So a table or view can have the same name if they are in a different schema.
Is this ...
0
votes
1answer
29 views
Rows to EAV structure with row identifier
Would you help me with this, please?
Imagine any SQL table or query results like…
+-----------+----------+-----+
| FirstName | LastName | Age |
+-----------+----------+-----+
| Peter | Whatever |...
0
votes
1answer
30 views
Query to get dates NOT in table
I am building a booking app and part of this involves reserving a courtesy car.
Courtesy cars can be booked out for a period, e.g.
2nd April - 11 April
12 April - 19 April
etc...
These dates are ...
0
votes
1answer
27 views
How to use variable in Exec statement [closed]
I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server ...
5
votes
1answer
101 views
Is Scalar UDF inlining broken?
This SQL code returns "a" instead of "asdfg":
CREATE FUNCTION [dbo].[GetPayload2] (@ID int) RETURNS VARCHAR(300) AS
BEGIN
IF @ID = 1
RETURN 'asdfg'
RETURN ''
END
GO
SELECT dbo.GetPayload2(1)
...
2
votes
1answer
44 views
Extract Year only from 'Select @@version' output
I'm trying to automate a short task in configuring our SQL Server for which I have to grab SQL Server year. Is there a way to extract year only from the output of following query: Select @@version ?
...
-1
votes
1answer
64 views
Improve performance in Deletion Operation
I Am doing Import Process of half a million records.
Initially i will move the .csv file data to a sql table,then i will parse the records (Split the records to different tables and then parse) and ...
1
vote
1answer
35 views
Adding a date and the time portion of a datetime2 together [duplicate]
I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2. I want to combine load_date with the time portion of load_time and create a datetime2.
It's ...
0
votes
2answers
44 views
MSSQL NULL's Between specific dates
I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s).
I also show companies that have 0 approved, declined and failed ...
0
votes
1answer
31 views
Calculate start and end dates from a list of only start dates and partitioned
I'm currently working with a data set with the following table structure
create or replace TABLE FACT_TBL_PRODUCTSUPPLIER (
DATEID NUMBER(18,0) NOT NULL,
PRODUCTNUMBER NUMBER(38,0) NOT NULL,
...
1
vote
1answer
51 views
Nested stored procedures with use of transactions
I have a question regarding this tsql-code found in this thread regarding "nested transactions" (unfortunately my reputation is not large enough to comment in that thread):
In what cases a transaction ...
0
votes
1answer
47 views
SQL Server Automate refresh of one table only from one environment to another
I'm looking for a best practice/option to refresh one table only from Prod to Staging environment.
Technical Details:
The table in Source is loading daily with new data.
The table in destination ...
-3
votes
1answer
36 views
Store procedure Incorrect syntax else [closed]
Could I be missing a condition inside my stored procedure? I'm getting this error:
Incorrect syntax near keyword else line 41.
use eNtsaRegistrationDB;
create proc[dbo].[...
0
votes
1answer
25 views
Error during sp_refreshsqlmodule after altering table type
I am trying to add a column to a user-defined table type.
I have done some research and here is what I have now:
-- First, rename existing table type to something else
EXEC sp_rename '...
8
votes
2answers
3k views
Dodgy T-SQL Query Execution driving me crazy
SQL Server 2019. Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of ...
0
votes
1answer
25 views
SQL stored procedure to produce custom XML text
I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.
With my current solution I have a stored procedure for each 30 tables where I do: ...
6
votes
3answers
295 views
Finding Missing Inverse Relationships
I'm struggling with a query which I feel should be straightforward but I just can't get my head around it.
We have a single table that holds relationships between different parties. The table SHOULD ...
0
votes
1answer
41 views
Duplicate record inserted - why trigger is not preventing it?
I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional ...
0
votes
1answer
54 views
Can't help the engine to choose the correct execution plan
The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.
Environment:
SQL Server 2016
standard edition
Objects:
wide table with ...
1
vote
2answers
42 views
How to sort values starting with a letter followed up by a number?
I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on.
How can I sort it so that first the letter is sorted, and after that the number with the ...
-1
votes
1answer
26 views
Help with joining to date table
There is an existing report that takes two parameters (BeginDate, EndDate) from the user. When a user inputs two date ranges, it produces a result in the LeadTime column of the difference between the ...
0
votes
1answer
31 views
How to return JSON objects in tabular format using T-SQL?
I am storing student data by group (or class) and week in the following format:
where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can ...
0
votes
1answer
49 views
Distribute values equally
Each ship docks at a port. If too much ships arrive at a single port the crew gets overwhelmed.
How to return a result set so a port does not get overloaded?
One possible solution and set based ...
1
vote
1answer
29 views
Querying XML Nodes with SQL
I think I'm nearly there with this but have reached an impasse!
I've a relatively straightforward XML structure that I need to parse out as a SQL resultset but can't seem to get it to work.
Here is ...
1
vote
1answer
33 views
Subtracting With GetDate()
I am trying to convert a postgresql query to ms sql query. This is what I have in postgresql
round(cast((Current_Date - emp.hiredate) As Decimal)/365,1)
now I thought it would be a simple ...
0
votes
2answers
43 views
Performing Sum in Case Statement
I am trying to get a sum inside a case statement, but I am getting the error
Cannot perform an aggregate function on an expression containing an aggregate or subquery
This is my query syntax
...
-1
votes
1answer
21 views
Using Sum With Query
How can I get the Sum of data from a secondary table w/o using a join?
Select emp.ID, emp.fname, emp.lname, emp.address1,
emp.address2, emp.city, emp.state, emp.zip, emp.phone,
Sales = SUM(Select ...
0
votes
3answers
107 views
How did you split strings before string_split()
I have some strings I need to split on a SQL Server 2014 instance.
I would normally do it something like this:
declare @bk nvarchar(1000)
set @bk = 'SIS5.0~person_id~696969'
select value from ...
0
votes
1answer
41 views
Copy last modified files from network path into SQL server box and rename them using Agent job
I have to copy latest backup from a network path and bring that to my SQL server physical drive for restore, I have to do that using agent job.
This is what i am doing.
Created a SQL server agent ...
0
votes
1answer
48 views
How to create a master key on the master database?What are the roles required to GRANT to the login and user of the master db for the same?
I am trying to create a master key on a master database of my azure sql datawarehouse. But my user does not have required permissions to do so. What are the roles need to be assigned/granted to the ...
0
votes
0answers
18 views
Is NOLOCK Select able to create incomplete rows [duplicate]
I'm hunting a bug in our software and wonder if a Select using NOLOCK could give incomplete rows.
Let's say my table has a row like
A A A A A A ...
and while selecting with NOLOCK an insert with
B ...
2
votes
2answers
83 views
How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database
I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to ...
0
votes
1answer
57 views
current_timestamp at time zone 'UTC' SQL Server equivalent
What is the equivalent of the Postgres "current_timestamp at time zone 'UTC'" in SQL Server?
How do I select the current timestamp in UTC in SQL Server?
-1
votes
1answer
44 views
Is there a good SSMS T-SQL code clean up / formatter plugin? [closed]
I'm looking for a good plugin that will automatically do things like upper case all keywords, indent lines appropriately, maybe brackets around reserved words being used as names / aliases, etc.
2
votes
1answer
419 views
How to force a sysadmin to store data in schema other than dbo? [duplicate]
I am using a GUI tool to upload data in my SQL Server 2016 database.
The user being used for above task has been granted sysadmin permission
I created a dedicated schema to store above data. This ...
-2
votes
1answer
77 views
Three updates in order in the same script won't work as intended, why?
I have a table with a field called gender. It's an int. 1 = male, 2 = female.
Here's what was done, in the same script
UPDATE table
SET GENDER = 9
WHERE GENDER = 1
AND (other criteria);
UPDATE ...
2
votes
2answers
61 views
Is it possible through only T-SQL to physically move the data and log files of a database from one drive to another drive (on the same server)?
I know you can alter the database to update their data and log file paths in the database properties, but is there also a way to script out the actual physical move of the files themselves?...maybe ...
0
votes
1answer
64 views
Where to put single quotes?
I am trying to execute the below statement which creates a database called service2019:
DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
EXEC (
'CREATE ...
1
vote
3answers
71 views
REPLICATE() return type for VARBINARY input
REPLICATE ( string_expression ,integer_expression )
As documentation says, it
returns the same type as string_expression.
I see that return type is always VARCHAR.
declare @vb varbinary(10) = ...
6
votes
1answer
197 views
Sometimes the updated rows are not locked within instead of update trigger
Here is as small repro:
create table dbo.t (id int primary key, v int);
insert into dbo.t values (1, 1), (2, 2);
create table dbo.s (id int primary key, v int);
insert into dbo.s values (1, 10);
go
...
