Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,720
questions
0
votes
0answers
8 views
Index automation job script
Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a ...
0
votes
3answers
65 views
Maximize production of log records
I need to fill in transactional log file in possibly shortest amount of time.
I have tried a few variants of a loop of inserts and deletes on the same record. I fired it on separate tables in several ...
1
vote
2answers
82 views
Index with few seeks and many updates [closed]
Say I create a nonclustered index on a table. After a week I look at the sats, e.g. number of seeks, scans, updates etc. E.g. say I have 10 seeks and 10000 updates. How can I measure the cost of the ...
0
votes
3answers
61 views
MSSQL - Convert overlaping datetime ranges into non-overlapping datetime ranges with respect to priorities
I have data stored in a table like this:
CREATE TABLE Records
(
[Id] int IDENTITY(1,1) not null
,[From] datetime not null
,[To] datetime not null
,[Priority] int not null
)
Every row contains ...
0
votes
1answer
23 views
How to join 2 tables when second table will return more rows?
im trying to join 2 tables with condition - if it matches than im expecting to get all results from another table.
Current querry that doesn't work :
SELECT (CASE WHEN ZL.NumerCzesci LIKE '%ZŁOŻENIE%' ...
1
vote
1answer
23 views
Accessing Memory Optimised Tables Under Snapshot Isolation
We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains.
Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view ...
0
votes
1answer
29 views
Deny DDL_ADMIN permissions to a role on [dbo] schema
I have several database schemas like,
[ext],[stag],etc
and of course the built in [dbo] schema,
and also have a role called [MyRole].
My plan is to add [MyRole] to the DDL_ADMIN role, so they can ...
-1
votes
2answers
53 views
Index usage across databases [closed]
I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server.
I have a newly created central database where data is replicated to from ...
0
votes
0answers
55 views
T-SQL Alternative to using XML CROSS APPLY [closed]
I have the following snippet from a long piece of SQL (that works fine)
declare @CONS as varchar(50)
set @CONS = ''
set @CONS = @CONS +''+'99'+''
...
...
left join dbo.TableA u2 on u2.UserOID in (
...
3
votes
3answers
550 views
How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?
The below is a valid TSQL statement. But I want to understand how SQL Server processes it.
DELETE A
FROM table1 WITH (NOLOCK)
WHERE
EXISTS
(
SELECT 1
FROM
table2 B WITH (NOLOCK)...
5
votes
2answers
145 views
SQL Server 2019 column store indexes - maintenance
I have a clustered columned stored index on table used for logging - only inserts (but not bulk inserts).
The current table stats are:
3541 millions rows
6.6 GB reserved space
I see this morning the ...
0
votes
1answer
50 views
Joining of two tables, one containing a large amount of data. Need help optimising the query
I have been given a job of taking two tables (trimmed down create scripts below) and merging their data into a third table.
CREATE TABLE dbo.Ztest_nominal
(
BMCode varchar(20) NOT NULL,
...
1
vote
2answers
54 views
ORDER placement for trigger in t-sql
I'm using Sybase ASE version 16.0.0.1915, (follows t-sql syntax) and trying to create a new trigger. This new trigger emulates another existing trigger, however is slightly modified. It would be a ...
1
vote
1answer
52 views
How to know a backup command failed or worked in a script [duplicate]
I have a command in a script to do a 'backup database [name] to [file] and all the other parameters.
Now, we have a problem where we run out of disk space, the script keeps on trucking, just issuing ...
-3
votes
1answer
48 views
How to represent rows as column from details table? [duplicate]
I work on SQL Server 2012 and I have an issue: I can't represent rows as columns.
I need to represent code type from details table to display as column with values of count parts from details table, ...
1
vote
1answer
44 views
How can I exclude rows from two tables whose values are encompassed by each other?
I have the following two tables, Table A and Table B.
What I'm trying to do is return a result set where:
If the start position (only) in Table B is between the start and end position of any row in ...
-2
votes
1answer
32 views
CONCAT and CASE WHEN combined
I'm trying to create a query that concatenates the directions field that were involved in each transaction_id.
I've tried to do it creating a flag table with multiple case whens, and then querying on ...
1
vote
2answers
37 views
DBCC CloneDatabase - sys.partitions rows value wrong
I'm using DBCC CLONEDATABASE to create a shell of a schema.
One of the side-effects of the new shell clone database is that I left with wrong values at sys.partitions.
as a matter of fact, the values ...
6
votes
1answer
140 views
Updating an indexed view with NOEXPAND
I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow.
I am not doing this simply to get more views on the question, I would like the DBA community to ...
-1
votes
1answer
39 views
Split data across multiple rows in a single statement
I have data in table like
Customer
Invoice No
Date
Value
Paid
Balance
ABC
1
01/12/2020
25
0
25
ABC
2
01/12/2020
50
0
50
XYZ
3
02/12/2020
200
0
200
XYZ
4
04/12/2020
100
0
100
ABC
5
04/12/2020
500
...
1
vote
2answers
43 views
SQL SERVER Detailed Backup Report
I need to create a script that gives the report for the following information:
Backup Job Name
Databases that have been backed up in the last 24 hours
Size of each DB backup
Start time of backup
End ...
1
vote
2answers
43 views
How do I add Median to this query?
I would like to add median to the following query, the median obviously will for each type of bedroom, unittypeid (already in the group by), I tried working with PERCENTILE_CONT but I couldnt figure ...
0
votes
1answer
27 views
T-SQL code to see Windows Folder access
Does anyone know how to check from T-SQL code in a stored procedure if a SQL user have read access to a WIN folder that is mapped to the SQL server? Thx Erik
0
votes
2answers
30 views
In which order does a SQL Replication write the data
I use a unidirectional Replication of a database.
In the Database I have a column with the datatype "timestamp" (not datetime).
When the replication is initiated it writes table after table ...
0
votes
1answer
40 views
copy data from Previous non null rows and if first row is null copy next non null value
I need to copy data from rows with non-null rows and if first row is null copy the next non-null value.
it is not a big table. There is no ID column and date column.
The datatype of this column is ...
0
votes
1answer
25 views
Query data in a BOM hierarchy
I have a data table with about the following schema:
ID (Int) --ID of the entry
Parent_ID (Int) --ID of the parenting entry, or 0 if there is none
Level (Int) --Level in the ...
4
votes
1answer
60 views
Skipped rows or rows read multiple times with NOLOCK
Is it possible to stimulate a situation where rows either get skipped or get read multiple times in a select statement? E.g. the way we can cause a blocking or a deadlock...
0
votes
1answer
23 views
Why DMV does not show index on my table
I am using this query to get information on an index on a table. I can see the index in SSMS.
select object_name(object_id), *
from sys.dm_db_index_usage_stats
where database_id = DB_ID() and ...
0
votes
1answer
33 views
Unusual behaviour of a query when using case expression in group by, some records are being missed
I have 4 table with below structure:
Table "Customer_Info"
create table Customer_Info
(
Customer_Number int,
Customer_Issued_Branch int,
<Other Columns related to customer&...
1
vote
1answer
36 views
How can I transpose multiple unrelated columns into rows in SQL Server?
I have a table that looks like the following:
PersonId EyeColor HairColor FavoriteTVShow FavoriteMovie FavoriteFood BirthInfo
---------- ----------- ------------ ------------------- ...
0
votes
1answer
22 views
Generate script for all stored procedures in Azure SQL database
I'm trying to create a SQL script that will create all the stored procedures that were exported from another database.
Basically navigating in SSMS to the database > Generate Scripts > select ...
1
vote
2answers
17 views
BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1
I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. ...
1
vote
1answer
67 views
Graceful Test for Linked Server Availability Inside a TVF or View
I’ve got a mission critical table valued function that needs to return results from several linked servers simultaneously. If any of those servers are down, it still needs to return results from the ...
0
votes
2answers
64 views
Clustered index in ETL (drop - insert - create?)
I have a table which is 130 milion rows. Every day I extract data from a source system and insert into this table. The extract is incremental i.e. I only get the new data from the source. The table is ...
1
vote
2answers
48 views
list of views that depend on a given table and column
I need a script to check if a given column in a given table is used in any views. Ideally I would like to get the name of the view(s) as well.
I am using T-SQL, and I need to run the script to check ...
2
votes
3answers
251 views
Query to update one column of a table based on a column of a different table
I have two tables with below structure :
Create table Customer_info
(Customer_num varchar(50),
Customer_Branch int);
Create table Customer_Transaction_Info
(Customer_num varchar(50),
...
1
vote
4answers
84 views
Reclaiming space after delete statement in SQL Server
I have some tables used in reporting and some of them grow huge with the daily ETL so I have implemented som jobs that delete rows more that x days old. E.g. everyday 10% new data gets added and I ...
0
votes
1answer
44 views
SSIS Package Duration is big before it begins executing
hello i have this question.
From Visual Studio i have deployd package in Sql server and i am manualy executing Package.
BUt i have noticed that package need 6 sec to do all things but Duration is 8 ...
0
votes
2answers
51 views
Grouping/sorting performance choice between bigint and nvarchar
I want to store a hash-code for a variable-length text field (max 1000 chars) in a database table. The hash-code will be computed and assigned once on insert, and new rows will be inserted very often.
...
1
vote
1answer
59 views
What would be the best way to conditionally insert or update a table that has this unique design?
I have an items table that has columns for person ID, position #, and individual lists of items that really aren't associated with the other fields in the record. I have no control over the structure ...
0
votes
2answers
38 views
Parent/Child summary rollup
I'm struggling to wrap my head around this one. I'm sure someone will simplify it.
We have a parent/child relationship that I'm needing to roll-up based on sold quantities and sales dollar amounts.
We ...
0
votes
1answer
35 views
Move database physical location using a loop for database names. MSSQL
I have a main database and several other databases that store documents in them.
I am trying to figure out a way to write a loop in my database move script that dynamically loops through the document ...
0
votes
2answers
27 views
Backup database using logical backup device in T-SQL
I am new to SQL Server coming from a software development background.
I created a backup device which creates a backup with the date and time appended to the name to make it unique:
DECLARE @...
1
vote
1answer
77 views
Filter rows if value exists in different columns
We're on SQL Server 2016 and 2019
I have a data-set where I'm rolling up a parent/child relationship. I'm doing this because some of the parent rows will return a SalesValue of 0 when their children ...
0
votes
3answers
27 views
Query to fine “Order_num” that has no two equal “product_name”
I have a table with below structure:
create table A_TEST
(
order_num int,
product_name varchar(50),
product_amount int,
product_price int
);
Sample data :
insert into a_test (...
0
votes
2answers
70 views
How to tell if SQL is autogenerated by some tool [closed]
I am looking into some stored procedures and the code is more or less non-readable e.g. there are 15 main joins in one statement and the level of nesting of the subqueries is e.g. 7 in order to simply ...
3
votes
1answer
79 views
Recursive CTE Multiple Levels [duplicate]
I have a table showing which roles recursively grant access to which resources within a database. For example:
The Default_Role grants access to the App_Role, the App_Role grants access to the ...
1
vote
1answer
38 views
Will SQL Server check FK restrictions between row deletes in a single DELETE statement?
I have a table that is self-referencing:
CREATE TABLE [dbo].[TestTable] (
[id] [bigint] IDENTITY(100000,1) NOT NULL,
[referenced_id] [bigint] NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY ...
0
votes
1answer
39 views
Can I just restore the .diff file over full backup to speed up restore?
I'm doing a server migration, and i'm looking at ways to speed up restore.
I have a 1TB full backup file which takes about 2 hours to restore
RESTORE DATABASE [MyDb] FROM DISK = N'C:\db\202010152000....
1
vote
1answer
64 views
Job waiting for resources - is it better to kill it or let it wait
Our server is very busy sometimes and I can see my jobs are waiting for resources. Is it better to kill it and start it after e.g. one hour or let it wait until it finishes?
Right now there is a rule ...
