Questions tagged [t-sql]

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

Filter by
Sorted by
Tagged with
0
votes
1answer
35 views

How can I apply the same logic to multiple tables that have the same number of columns and data types?

I have multiple tables with identical number of fields (and data types). Here are two examples (DDL to create these tables at the bottom): Animal AnimalGroup Name ----------- ---------...
0
votes
0answers
39 views

Understanding CPU time to measure workload

I want to understand the workload of my stored procedures, which run on an instance with many other databases. Among other things I would like to know if I had my own server how many CPU core would I ...
0
votes
2answers
74 views

Query a list of objects in each database

I have a list of about 300 Views spread out across several databases. Some of the tables used in the DDL of the views have been dropped or renamed. What I want to do is try to query all 300 views in ...
0
votes
1answer
51 views

Getting data from Postgres through Linked server of SQL

I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query select * from openquery(SALESFORCE,'select closeddate from ...
1
vote
3answers
234 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
0
votes
2answers
74 views

Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool

I would like to be able to fetch the name of the elastic pool that the current database belongs to using TSQL but the only DMV I can find with a column containing elastic is sys....
0
votes
1answer
45 views

CREATE FUNCTION inside BEGIN TRY and END TRY

I want to use CREATE FUNCTION in a block that will "swallow" errors, but this BEGIN TRY CREATE FUNCTION test (@ID int) RETURNS int AS BEGIN RETURN(2 * @ID) END END TRY BEGIN CATCH END ...
3
votes
1answer
989 views

Azure SQL Database throwing error 1132 "The elastic pool has reached its storage limit" error, even if it's not full

I'm trying to execute the following (sample) batch in an Azure SQL Database inside an Elastic Pool: drop table if exists [dbo].[InsertTest]; create table [dbo].[InsertTest] ( [id] uniqueidentifier,...
4
votes
4answers
909 views

Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
-1
votes
1answer
51 views

Check if contract conflict with another contract query [closed]

I have a database with these tables as the bellow picture showed: The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts,...
1
vote
1answer
280 views

Sql Server pivot string results into columns

I am trying to transpose a dataset of string values, into columns, depending on the value of an integer example: create table imgText ( rowindex int, imgName varchar(50) ) insert into imgText (...
0
votes
0answers
21 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
35 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 ...
1
vote
2answers
535 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
51 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
38 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
47 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
96 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
60 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
122 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 ...
1
vote
1answer
120 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
101 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
243 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
639 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
91 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
70 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
38 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
105 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
88 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
96 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
54 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
35 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
218 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
54 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
180 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
100 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
2k 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
740 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
232 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
54 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
49 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
68 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].[...
2
votes
3answers
84 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
113 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
49 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
130 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 ...
1
vote
4answers
341 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
33 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
502 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
103 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-...

1 2
3
4 5
58