Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,778
questions
0
votes
0answers
19 views
Track usage of views and set up logging
I set up a database with some views. I want to log who accessed which view and when. The target-table looks like this:
[schema], [table], [user], [timestamp]
dbo, access, someuser, 2021-04-13 12:07:...
1
vote
1answer
21 views
Select distance between cities in two directions
I have a distance table and a cities table.
I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km
i need to get the distance betweens cityA and CITY B and
then ...
0
votes
0answers
20 views
Transferring records from one instance to another
I have 2 instances of SQL Server 2016, SQLDB1 and SQLDB2. So when a job fails on SQLDB2 instance I want to have a entry for that job failure with the timestamp on a table called "monitor" on ...
1
vote
2answers
32 views
Choosing values from multiple columns based on latest value of one column
I have an Azure Cosmos DB that contains columns DateTime, userName, temperature, and bloodPressure, plus many other columns. There are several distinct userName values, and each userName has ...
-2
votes
1answer
45 views
Would like to see what Credit Card Numbers have changed [duplicate]
Using the following SELECT Statement, I see all active drivers with a Credit Card Number :
SELECT top 1000 *
FROM cashcard
WHERE crd_status = 'A'
Based on that query, I get a table with a bunch of ...
0
votes
2answers
27 views
How do I find out whenever a value changes in a certain column?
Need help if anyone can offer!
I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about ...
0
votes
1answer
40 views
SQL Server - Round a number when it's one decimal before .50
I would like to know if there is a way to round a number if it's one decimal before 50.
i.e,
I am using the below code and it works, it gives 8.50:
select Round(8.49,1) = 8.50
The problem comes when ...
0
votes
1answer
61 views
Most efficient way to compare strings in SQL Server 2014?
Hope everything is going ok.
So I'm in an awful situation. I have these two tables, and I need to check that there are not duplicated people. The duplicates are shown to the user in the front end:
...
0
votes
2answers
55 views
How to see if index is being read “ordered” from DMVs
I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can ...
0
votes
2answers
43 views
How to see who made a change to a table in SQL Server
Is it possible to see who changed a table (or a stored procedure) in a database when the recovery model is simple? E.g. length of data type...
If the recovery model is full will it be possible so see ...
0
votes
1answer
63 views
Sp_BlitzWho can get blocked…?
I was playing around with sp_BlitzWho and got blocked by another session. The session was from the DB were sp_BlitzWho is... I was planning to automate it and save the results to a table, so I could ...
0
votes
1answer
35 views
List all users and permission
I found this script and find it is very helpful to me. I am new here and not good in coding. Can someone explain to me the below script the **'{All Users}'means? represent of? Appreciate your answer ...
9
votes
2answers
209 views
Why is SQL doing an index scan and seek with the same XML query structure?
I've set up a test to benchmark xml performance in SQL server.
Test Setup
One Million rows of data
XML defined Column with primary key
A primary XML index
A secondary XML index on the path
XML data ...
2
votes
2answers
550 views
Are SQL unit tests supposed to be so long?
I am writing stored procedures with some non-trivial business logic. I am trying to unit test them, but the actual tests end up being quite long (shorter ones starting at 40-50 LoCs, using commonly 4 ...
2
votes
1answer
83 views
Get every column in database that contains only Null records
I am responsible for a poorly designed legacy application that has 700+ MSSQL tables and plenty of columns that are completely unused. I would like to run a script to return the column and table name ...
-1
votes
1answer
64 views
How to kill agent job if it does not finish after x min
I want to create an agent job and collect some data on my server, e.g. every 10 minutes. But I would like to kill the job if it does not finish within a specified time period, e.g. after 5 min. I have ...
0
votes
1answer
22 views
How can I output text until it is ascii in SQL?
How can I output text until it is ascii in SQL?
Here is what I am trying to do:
DECLARE @input VARCHAR(20)
SET @input = 'text'
DECLARE @index INT
SET @index = 1
DECLARE @output VARCHAR(32)
SET @...
0
votes
3answers
35 views
How to select multiple rows using one query
Is it possible to select multiple rows using one query in SQL Server? For example, I have to run two queries to get my results as follows. How can I achieve the results using one query?
SELECT col1, ...
0
votes
1answer
29 views
Truncate and insert: Heap and build index or directly into Clustered table
In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place.
I know that I can ...
2
votes
1answer
77 views
How to create development/test environment of TB reporting database in SQL Server
I am working on a couple large reporting databases with lots of reporting and analytical queries and many ETL jobs. When I make changes I usually do in in production, be that changes in indexing or ...
1
vote
1answer
40 views
Avg write latency spikes for disk more than usual. Need help finding the culprit
We recently had increased in disk write latency reported. Though from our monitoring team it was not confirmed from which drive but doing some dmv query I see tempdb around avg 32 ms wait on write. ...
0
votes
1answer
30 views
In SQL Server, how do I relate each date in a set to the subsequent date from the same set?
I have a large table with a DATETIME2 column. I would like to write a query that relates each distinct date in that column to the date from the same column that immediately follows the first date. I ...
1
vote
1answer
32 views
put the result of a query into a variable and WITH statement
I need to put the result of a query into a variable.
Just a query, works as successful
DECLARE @count INT = (
SELECT count (*)
FROM [AdventureWorks].[Person].[Address]
);
select @count;
But if ...
-1
votes
1answer
43 views
When was my indexes in the database fragmented?
I am trying to create a script that finds the index fragmentation, have it log to a table that I can query each time it runs. I need to fins out specifically what time my indexes in a table get ...
1
vote
3answers
84 views
Implicit conversion: when does it hurt performance a lot?
I know that when data types are the same it is good and it hurts performance when they are not.
But sometimes it seems to have very little impact and basically a waste of time to fix it. Other times ...
2
votes
1answer
69 views
Forwarding records in #temp table
I have a temp table which I update many times based on joins (each time almost all the rows). I have kept it as a heap since if I don't get an inner loop join then after each update I get a sort in ...
0
votes
2answers
44 views
How to Duplicate Column in same table in SQL
I'm pretty basic with SQL and need help with the following task:
I already have a table with data in it. What I would like to do is to Duplicate a Column (Including the data) in the same table but ...
0
votes
1answer
52 views
Azure SQL | Granting Security Groups DB Role permissions
I am trying to grant a Security Group within our company's Azure environment that is found in Azure AD. I have successfully granted users' rights to the db_datareader, writer, owner roles. But I am ...
2
votes
1answer
59 views
How do I convert a TIMESTAMP to a BIGINT in Azure Synapse
What's the best way to convert a TIMESTAMP to a BIGINT In Azure Synapse. I tried this (which works) but seems clumsy. Is there a better, more concise and efficient way of doing the same thing?
SELECT ...
0
votes
3answers
45 views
How to perform bulk delete and release free space?
Good Afternoon,
We have a database that wasn't been an archive anytime. The database has grown tremendously and we are running short of space. Hence there is a need to do a bulk delete and free space. ...
1
vote
1answer
37 views
T-SQL: Assign identifier to block of results
I'm not sure exactly how to phrase this question but... I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each ...
1
vote
3answers
61 views
SQL Server - Unique Constraint Syntax
Is there a functional difference between the tables built in the following two ways?
CREATE TABLE [dbo].[test_uq](
[ID] [int] NOT NULL,
[f1] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[...
1
vote
3answers
60 views
Shorthand for “UNIQUEIDENTIFIER” data type name?
The data type keyword "UNIQUEIDENTIFIER" is a chore to type. I want a shorthand replacement.
At first glance, it seems that SSMS provides two; the words "GUID" and "UID" ...
0
votes
1answer
52 views
Partition switching vs schema transfer
I'm designing a database whose data will be generated daily by an ETL.
After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live&...
2
votes
2answers
47 views
How does JOIN involving database across 2 servers store the intermediate data?
In case of a SQL JOIN on 2 databases across servers.
For example:
Select *
From server1.db1.dbo.tbl1 a
Join server2.db2.dbo.tbl2 b
On a.common_id=b.common_id
Where a.some_col = 1000
Suppose this ...
1
vote
2answers
68 views
Can you update a database with transaction logs only, without having to do a full restore?
Initial note I am not a DBA, I am learning on the job.
Where I work we have an antiquated third-party system that sends us a .bak file once a week and log files every hour. To get the previous day's ...
0
votes
3answers
45 views
How to write and read files from client machine SQL Server 2019
I need to prepare a stored procedure which export as many files as many tables I have in schema, but files need to be written on local machine on specific path e.g. C:\Temp\ not on SQL Server ...
0
votes
1answer
28 views
SQL Server - ensuring a complex relation on insert
I have some related tables:
Application - stores a list of applications
ApplicationInstall - for each application, multiple installations are present (I have a foreign key ApplicationUid)
...
1
vote
1answer
48 views
In SQL Server, what's the best way to split multiple delimited strings into rows without a function?
I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a ...
0
votes
0answers
38 views
Get actual Job Name of the running SSIS package from Program Name column of the results while running sp_whoisactive or sp_who2
When I run sp_whoisactive or sp_who2 and if there is a SSIS Package running it would always display in the Program Name as the name of the running SSIS Package like SSIS-Package-{063BC46B-1F74-4207-...
0
votes
1answer
50 views
SQL Server Query column from Select
Can anyone please guide me what's wrong in this SQL Server query:
SELECT
A.*,
(SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) Name
FROM A;
It seems that A.Name is inaccessible in ...
1
vote
3answers
64 views
Get actual Job Name from Program Name column of the results while running sp_whoisactive or sp_who2
I am running sp_whoisactive on my Prod and offload the data for analysis.
When I run sp_whoisactive or sp_who2 and if there is any SQL Agent Jobs running it would always display the Program Name like ...
-1
votes
1answer
55 views
TSQL throw weird error when commenting a line of text [duplicate]
Here is a strange situation. Following code works perfectly if I run it as is, but if I comment the first CHARINDEX line, it throws an error.
Msg 536, Level 16, State 4, Line 1
Invalid length ...
0
votes
1answer
45 views
Delayed durability for reporting database
I have a small database which basically run a couple of reporting SPs. Data gets extracted from an OLTP database and refreshed everyday and most tables in this database get truncated everyday.
The ...
3
votes
1answer
57 views
Why is log reserve negative in the SQL Server compensation log when using fn_dblog command
I was using the fn_dblog command and was not able to get why the log reserve was negative.
0
votes
2answers
47 views
Merge two databases in SQL Server
I have some databases on an instance used for reporting. I want to merge them into one. There are many cross-database dependencies in the stored procedures (e.g. an SP can use tables from different ...
0
votes
1answer
7 views
Query To Show All Users Who Have Multiple Managers
I have a database that holds user information. I need a way to find out what users have multiple managers. Below is a subset of data to illustrate the data structure.
Using MS SQL Server 2016 what ...
-1
votes
2answers
58 views
Transaction log: small transactions vs large transactions for speed [closed]
I am trying to learn how the transaction log works and have been studying it for a couple of day.
Some operations become faster when done as many small transactions, e.g. deleting many rows in small ...
0
votes
2answers
51 views
Transaction log space used in percent - how to clear or empty
I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications.
So I tried below:
select * from sys....
0
votes
0answers
31 views
Joining without a unique identifier
Payments
PaymentRef
Product
PaymentAmount
MerchantRef
P01
ABC
100
MR01
P02
ABC
200
MR02
P03
XYZ
200
MR03
Refunds
ReundRef
Product
PaymentAmount
MerchantRef
R01
ABC
-10
MR04
R02
ABC
-100
MR05
...
