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
54 views

Determine the source of user permissions in a database

I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment. How can I track down what security ...
2
votes
2answers
146 views

Database cannot be opened. It is in the middle of a restore

I have executed these two commands: USE master GO RESTORE DATABASE test FROM Kingston WITH NORECOVERY GO USE master GO RESTORE LOG test FROM Kingston WITH STOPAT = '2020-05-13 14:05:25', RECOVERY; ...
0
votes
3answers
51 views

Problem with IF statement [closed]

I am having an issue with this stored procedure. When the user selects "Yes", it should do what it says in the if statement for the "Yes" but it does not do anything, it does not insert anything into ...
1
vote
1answer
43 views

Does “Table Valued Function” force the plan to get into cache?

sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
0
votes
1answer
76 views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as ( ...
0
votes
1answer
63 views

Calculating the total row size of a table - SQL Server

I have a table 'Connections' with the following schema in SQL Server: [ID] [int] IDENTITY(1,1) NOT NULL, [User] [nvarchar](20) NOT NULL, [UserID] [int] NULL, [True] [bit] NOT NULL, [Comment] [...
0
votes
1answer
27 views

Add Leading Zeros in CASE, CHARINDEX and SUBSTRING Query

I am splitting a column based on the colon using this code which works. ,CASE WHEN CHARINDEX (':', ref) > 0 THEN SUBSTRING(ref, 1, CHARINDEX(':', ref) -1) ELSE NULL END AS EndVal The '...
2
votes
2answers
47 views

Select rows based on values from 1 column being the same?

An example of my database table film id | scene id | value id ----------------------------- 0 | 0 | 7 0 | 1 | 1 0 | 1 | 0 1 | 2 | 8 ...
4
votes
1answer
183 views

How to get list of users on all instance's databases? excluding the mirrors!

For an audit of our MSSQL databases, I have been asked to provide a list of all users for each database running on each instance. There is an existing stack overflow question which advised to use......
0
votes
2answers
30 views

Query to find the 'Branch_Code' of the 'Channel_Num' with the lowest 'Issue_Date' using T-SQL

Hope you're doing well I have a table with the structure below : Customer_Channel_Branch:(Customer_Num int ,Channel_Num int ,Branch_Code int ,Issue_date datetime) Some Example Data : ...
3
votes
2answers
43 views

Find order that has deliveries in multiple weeks

I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it. Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the ...
0
votes
1answer
118 views

Validation logic for Mobile Number Column in SQL Server

I am creating a validation script and need help with mobile number validation logic with below details. Mobile number must be: 10 digits in length. Cannot begin with a 1 or a 0 (For example:...
2
votes
1answer
66 views

Performance of query against column level dependencies much faster when using deprecated system view

Consider these two queries that get the names of triggers dependant on the JV_VoyageFlight column in table dbo.JobVoyage. the first uses the 'old' mechanism that is deprecated (sys.sql_dependencies). ...
1
vote
1answer
37 views

Fetch data if exist in distinct of another column

I am trying to generate the query to fetch the data on the basis of company which have tax paid record in each year of : Select * From ( Select CompanyName , TaxPaidOn , ...
0
votes
1answer
15 views

Have x number of rows returned for one record

I have a Dates table with every single date and a column called weekly, biweekly or monthly . I want to show my data as it would appear if it were scheduled ex id date weekly biweekly ...
-1
votes
1answer
25 views

how do i reference a cursor defined in a database via another database's stored procedure

I have a cursor as part of a process that removes the files of a specified database file group. I want to code this in a stored procedure that will exist in one database and reference another ...
0
votes
0answers
38 views

Review of scheduled bulk deletion on a production database

I have a EU privacy requirement to delete production data regularly. The script runs weekly when the traffic is quiet. I wondered if there is any way to improve this script. I can't delete directly ...
0
votes
1answer
25 views

After a point-in-time restore, is there any way I can get the transactions during restore time?

The hypotethical scenario: Imagine that I have to do a point-in-time restore of a Azure SQL database, the original one is called DatabaseA and the restored is after restore called DatabaseA_restored. ...
0
votes
1answer
24 views

Sql Statement to display multiple true statement

I have a sql statement that checks for certain special characters in a string and returns that. SELECT CASE WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], '...
1
vote
1answer
39 views

How to use Common Table Expression properly - Example where it seems to be very slow in TSQL

I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp ...
1
vote
0answers
16 views

Removing files from a filegroup

I am writing some T-SQL code to retrieve the file names that make up a file group so that I can remove the files and the related filegroup. The way that I coded it is below: set @cnt = (select ...
0
votes
2answers
66 views

Slow Trigger performance when big batches

I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when ...
0
votes
0answers
31 views

Problem with installation Polybase in SQL SERVER 2019 on local instance

I am trying to install Polybase on SQL Server 2019 but in process of instalation I got following error: [ZARZĄDANIE NT\USŁUGA SIECIOWA] is [NT AUTHORITY\SERVICE]. I was also trying [NT AUTHORITY\...
0
votes
1answer
62 views

How to calculate a value for all rows in sql server?

I have two tables. One is a linking table to handle a many-to-many relationship between users and construction projects, and the other one is a payment table which includes the users' payments. Now I ...
0
votes
1answer
46 views

Can someone help me with the SQL Server syntax for the my Oracle query

Oracle version: CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, p_b1 IN NUMBER, ...
0
votes
1answer
22 views

How to name tables which are responsible for storing dictionaries and configuration?

Below I attached diagram which corresponds to part of my database : My questions: In your opininon Do I have to divide ServiceInsance and ConfServiceInstance ? I did it like that because I would ...
0
votes
0answers
25 views

How to improve query performance sort operation overloads query

I have simple database which is consist from two tables. I want to build simple query that will return ForumId - Forum id (Topics). Title - Title of last post (Posts). DateTime - Datetime of last ...
0
votes
1answer
43 views

Best way for naming schemas in SQL Server 2019 [closed]

Would like to ask about using of schemas in Sql Server 2019. For now we are using numbers as schemas for procedures and functions: 0,1,2 etc. to version procedures and functions. For example [1].[...
1
vote
1answer
23 views

Tsql query help [closed]

Can someone help me with a tsql query please. I am look at the dbo.backuphistory table in msdb. I want to find the average duration for each database and then total time of all databases. Can someone ...
0
votes
3answers
47 views

before update trigger on sql server

I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and ...
0
votes
2answers
17 views

T-SQL Update Random String Value in Column

I have a table that contains a file path column containing data like this. C:\ABC\Files\AA.dat C:\ABC\Files\AA.Unbound.qp C:\DEF\AA\GGG Build 1 Modules\Random123.qpp C:\DEF\AA\GGG Build 1 Modules\...
0
votes
1answer
30 views

Method for Finding Top Record with WHERE conditions in Child Tables

Setup Recently I worked on an optimization for a SQL Query which returned the Prescription Product most recently used by a Patient. The original query handled this via 3 nested CTE's and had really ...
0
votes
1answer
33 views

How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?

We are dealing with very large tables in Sql Azure. Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends. Metadata operations are quick ...
0
votes
1answer
23 views

check format of every value of column in sql server

I have a table_1 that has column 'dob' which has dates in nvarchar(max). Now I want to check every date whether it is in datetime format or not. The dates which are in datetime format should be copied ...
2
votes
2answers
39 views

Converting properties to columns

I have a list of types: SELECT * FROM type; id name ----------- -------------------------------------------------- 1 person 2 other god 3 location 4 ...
0
votes
1answer
49 views

SQL Server column type = Date not accepting dates as varchar in insert statemant

In T-SQL, I am trying to insert values in a date table, US & French dates. When table is created with columns as DATE, the insert does not work, it errors. When I switch to VARCHAR(12) instead of ...
0
votes
0answers
18 views

Saving CSV data in a row with delimiters takes more time - Suggest any alternative solutions

create table dbo.import_temp ( IMPImportID int identity(1, 1), TemplateID int, UserID int, CreatedDate datetime, Status int, Data varchar, Message varchar, CommunityID int, ...
0
votes
0answers
28 views

UPDATE records in one table, several times the message (1 line affected) and then the actual message of the update

I have discovered the following oddity: When I execute a SELECT statement, I get my desired 753 records displayed. But when I convert this SQL to an UPDATE and I execute an SQL with the following ...
-1
votes
1answer
338 views

Stored procedure in SQL Server that returns total count of columns in table and distinct count of values in each column [closed]

I need a stored procedure that takes any table name as a parameter and returns total count of rows and distinct count of values in each column of that particular table when executed. Let's take the ...
0
votes
1answer
32 views

Group by with unrelated counts

I have three tables, one, two and three. one two three With this query select one.id, count(two.id) as num_twos, count(three.id) as num_threes from one left join two on one.id = two.one_id left ...
0
votes
1answer
34 views

Parenthese use in a SET statement

I am trying to compute the number of distinct pairs sender_id, send_to_id and assign it to a variable @UniqueRequests. When I try the statmement below, I get an error saying the syntax near ) is ...
-3
votes
1answer
27 views

Convert a query results from lines to columns so i can see somes values from a column in columns

Just need to have just 1 line from this query (that returns several lines due to the columns "Questão" to right. I'll need to have each value of that columns in a column so i can have just one line ...
0
votes
2answers
18 views

Combining previously different rows after changing value during query

I'm currently running a query that is supposed to return a salesperson's aggregated revenue, grouped by individual clients. Because of how user input was handled, there are a lot of near-duplicate ...
1
vote
0answers
30 views

Do I always need boilerplate code to prevent nested transactions?

Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one? It seems like a ...
0
votes
2answers
35 views

Insert Records That Do Not Exist

We are upgrading from one database to another. Both databases have the same tables, and for examples sake let's say we have dbOld and dbNew. I need a way of for each table on dbNew check the ...
0
votes
1answer
29 views

Massive Transaction Logs - what are my options?

I have a fairly crazy scenario where I am processing ~250 million inserts and 250 million deletes for a table that contains data that is only relevant for the moments that it is in the database. Once ...
0
votes
1answer
56 views

T-SQL query to show table definition of sys.time_zone_info

Following query could be used to show definitions of custom table. SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH , IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE ...
-1
votes
1answer
131 views

Lock differences between Insert Into vs Select Into while using Openquery

Using SQL Server 2014. My DBA said I should be using Insert Into instead of Select Into when using Openquery because Select Into will create a lock on the linked server table. Is this true? linked ...
18
votes
3answers
2k views

Strange JOIN ON clause in T-SQL

I am untangling some legacy code and never in all my years have I seen this: Select * FROM GLAccounts INNER JOIN GLCharts ON glaGLChartID = glcGLChartID LEFT JOIN GLCategories ON ...
1
vote
3answers
115 views

How to copy database from one server to another with script

So I have a script that almost does what I want but for one server.I need it to work "Cross-Server" ex.I have a database in one server and I need to pass it to other server and deleting if it exists ...

1
2
3 4 5
53