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
-2
votes
0answers
22 views

stored procedure to create a variable for the two columns from different databases and make compare raise exception if don't match

I have two tables from different databases Table1 with fields flid(int), pid(int) Table2 with fields flid(int), pid(int) All I want is to create the variables with integer for two columns(flid)and ...
-1
votes
1answer
35 views

Suggestions to create a table or to design a stored procedure to remove while loops

The company I am currently working for, has a scenario for which we have to use while loops or cursors in SQL. Which has a huge performance impact in our case, hence, I want to optimize it. The ...
0
votes
1answer
38 views

CREATE DATABASE permission denied in database 'master'. Cannot attach

In a T-SQL script, using a connection string like this: Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Temp\ContextTest.mdf;Initial Catalog=ContextTest;Integrated Security=True ... I ... ...
1
vote
1answer
39 views

How do I prevent a column being replicated in SQL server?

I'm on MSSQL 2016 using push-based transactional replication to an Azure instance. I have a few columns in our modeled data that I really do not want to replicate. Is there a way to flag a column as &...
-1
votes
0answers
78 views

How to replace clustered index with non-clustered fast? [closed]

Can clustered index be effectively used in this case? We have a large table that keeps logs of users’ actions. 189-190GB clustered index. We want to analyze data there daily and run more complex ...
2
votes
1answer
87 views

How to check which pages are part of an extent?

I have some data corruption and was not able to restore the data via backups. It was affecting mainly non-clustered indexes, so after drop-create of the indexes the issues were resolved. Finally, I ...
-1
votes
1answer
46 views

MaintenanceSolution Job stuck in executing state

Hi im currently using ola hallengren maintenance solution for running my daily backups but sometimes im having errors with the jobs getting stuck on executing state. but once i've killed the ...
0
votes
1answer
49 views

How to query database integrity check duration time (how much time did it take to complete dbcc checkdb) [duplicate]

How to know how much time did it take to run database integrity check for all databases on a instance. I simply need when it started and when its finished. I am able to query when the last check db ...
1
vote
0answers
62 views

Questions about execution plan

I'm reading "SQL Server Execution Plans, Third Edition, by Grant Fritchey". It includes the following query and the execution plan (queried against the Adventure Works database). SELECT soh....
0
votes
0answers
54 views

Unable to get FileStream Length with After Insert trigger

I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I'm populating ...
0
votes
0answers
29 views

tsql do update queries that join other tables lock the joined tables also?

Azure SQL Managed Instance. If I am updating a temp table, and joining to another table or tables, do the joined tables also get locked, even though the update statement is only updating the temp ...
0
votes
1answer
50 views

T-SQL : Grouping based on chronological switch periods

I have the below table extracted from a Type 2 Slowly Changing dimension. I want to group rows to only have distinct classifications and their respective 'switch' time periods in a chronological order....
0
votes
1answer
61 views

SQL case problem [duplicate]

SELECT CASE WHEN OfferSum.DayOfWeek = 'Monday' THEN 'Mon' WHEN OfferSum.DayOfWeek = 'Tuesday' THEN 'Tues' WHEN OfferSum.DayOfWeek = 'Wednesday' THEN 'Wed' WHEN ...
-1
votes
1answer
41 views

Clustered Index and Sorting [duplicate]

select * from [table_a]; A clustered index sorts the table based on whatever column(s) you choose. That being said, if I have a clustered index on [column_a], [column_b], and [column_c] and run the ...
0
votes
3answers
63 views

Database Sizes error; Arithmetic overflow error converting expression to data type int

I am using the below query to pull back database sizes for our SQL instances. This cannot pull data larger than 2TB back because of the maximum number the int data type can pull back. How do I go ...
0
votes
1answer
31 views

Update and Varbinary(Max)

I want to set the value of sequence from row 1 to row 2. If I run: Update [dbo].[TS_TaskSequence] set [Sequence] = 'the value of sequence' Where [TS_ID] = '16777234' I get: Implicit conversion ...
0
votes
1answer
37 views

Is this horrendously low row estimate causing a slow insert?

reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, ...
0
votes
1answer
23 views

How do you detect a change in grouping when using Partition By in SQL Server 2012+ and execute logic based upon the change in the same SQL statement?

If I have a SQL statement that contains something like, ROW_NUMBER() OVER(PARTITION BY school_area ORDER BY school_area) I get: 1 someschool1 schoolarea1 2 someschool2 schoolarea1 3 someschool3 ...
0
votes
0answers
24 views

How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?

Hope you are well I'm been a bit stuck trying to develop the code to get the inheritance of extended properties. I will explain the situation: The thing is have a query to list all extended properties ...
3
votes
3answers
135 views

Reducing Temp Table Scans when joining two temp tables using OR conditions

I'm working on a complicated query that I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
5
votes
2answers
391 views

Why does switching two column values work by simply reassigning the values in T-SQL?

For example, the following query works just as intended in Microsoft SQL Server (T-SQL). UPDATE Customer SET ContactName = Customer.City, City = Customer.ContactName; I would like to know why the ...
0
votes
1answer
74 views

Number of executions of the same sub query by the DB engine

If I have this SQL query SELECT (select count(*) from tableA) as cnt, t1.* FROM ( SELECT t.* FROM ( SELECT * FROM tableA UNION ALL SELECT * ...
0
votes
1answer
26 views

SSIS debuging tool in SSMS 2016

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...
0
votes
2answers
59 views

Difference between statement for declaring variable MSSQL

DECLARE @Variable AnyType SELECT @Variable = something OR DECLARE @Variable AnyType = something
3
votes
1answer
35 views

How to group data but taking rows only in a specific order (example should be clearer ;) )

I have something i need to do and i don't know how to do it :) I have the following data : Art Type 2215 Z 2215 C 2215 M 2216 C 2216 The result i want to have Art Type 2215 M 2216 C Those ...
0
votes
1answer
32 views

Do we have information about duration time of query in actual execution plan?

I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
12
votes
1answer
957 views

Seemingly identical WHERE clauses returning different results

I have a table named [User]. Below is a simple query to find one user from the table: SELECT [Username] ,[FirstName] ,[LastName] ,[EmailAddress] ,[IsStaff] ,[ExternalID] ...
2
votes
2answers
42 views

T-SQL Group and sum record set based on value in a given column

Take the following example: SELECT * FROM [Orders] WHERE DATE = '2021-09-10' Date FirstName LastName Adults Item Description ---------- --------- --------- ------ --------- -----...
0
votes
1answer
49 views

Select rows where result appears more than once

I have been looking all over StackExchange, testing various methods listed here, without any success. I need to SELECT only users that have multiple sessions. This is my beginning SELECT statement: ...
-4
votes
1answer
47 views

Updating column value across all SQL server versions [closed]

I have a simple table with 3 columns Installed by - some login name Installed date - Date when bunch of scripts were run with any changes as part of that package Version - version number when release ...
0
votes
1answer
33 views

What does T-SQL msdb.dbo.RestoreHistory Restore_Type NULL mean?

In msdb.dbo.RestoreHistory, the Restore_Type can be NULL. See https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql?view=sql-server-ver15 restore_type / ...
2
votes
2answers
87 views

Keep full history of every values in each table

How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify ...
0
votes
0answers
40 views

Creating sql server custom database role that only allows reading some columns and nothing else

I want to create an sql server Custom Database Role which only allows reading some columns of some tables, and nothing else. this is my attempt. CREATE ROLE CustomDatabaseRole01 GO GRANT ...
5
votes
3answers
349 views

Hovering over an Operator in Execution Plans: How to make the tooltip not disappear when moving the mouse

I want to share my screen and show an execution plan but when I hover my mouse over an operator and what to point to something with my mouse in the tooltip then it disappears. Is there a way to make a ...
4
votes
1answer
128 views

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...
3
votes
1answer
380 views

What are the .BCP files inside a .bacpak file?

Diving on dba.stackexchange.com I found a nice answer that teach me how to open a .bacpac file with File Explorer. I gave it a try with the database AdventureWorks2008R2 and all I did was: Save the ...
1
vote
1answer
54 views

Query to divide each column value to two categories: [Common , Not_Common]

I have a table with below structure and data: create table PTEST ( col_name VARCHAR(50), col_value VARCHAR(50) ) COL_NAME COL_VALUE ----------------------- first apple ...
1
vote
1answer
45 views

Separate Specific Indexes VS Single Inclusive Index

I have four tables, all of which have around the same large number of rows (except Customer): Shipping (PackNumber, PackLine, OrderNumber, OrderLine, CustomerID) Orders (OrderNumber, OrderLine) ...
0
votes
1answer
47 views

MSSQL Join to put rows into columns column variable as column name

I have two tables TABLE 1 PRODUCTID TITLE 001 Product1 002 Product2 TABLE 2 ID PRODUCTID VARNAME VARVALUE 1 001 url https://... 2 001 length 30m 3 001 somethingelse somevalue I am trying to ...
-2
votes
1answer
48 views

Delete millions of rows on a badly designed table [duplicate]

I have identified Millions of duplicated rows on a table I have inherited by using this query: SELECT COUNT(*) AS NumRecords, AccessID, LEFT(SQLTEXT, 5000) FROM Table WHERE AccessID=5012 GROUP BY ...
1
vote
1answer
57 views

Create a Sequence with the calling user as the owner

Background I have a SQL User that has the following permission: GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator GO I would like to be able to have this user create a sequence that is owned ...
0
votes
1answer
31 views

Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from

Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue ...
0
votes
0answers
118 views

Permissions to run SQL Server Agent Job

I am getting the following error message: Message Executed as user: fin\jdoe. The server principal "fin\jdoe" is not able to access the database "abc" under the current security ...
1
vote
1answer
162 views

Update Rows From Table If Table Exists Without Using sp_executesql

I have a situation where I need to run a query on a large number of similar databases. Most of them will have a table that I need to update some rows in but some of them will not have the table. I ...
4
votes
5answers
435 views

Handling optional parameters in stored procedure

I have a stored procedure, say "Test" with an optional parameter @Param1 int = NULL. In the procedure, the value of @Param1 is used to update the value of some column in some table, if the ...
1
vote
1answer
40 views

Is there any way to monitor if all columns in an article is published in replication

In replication, if we add a column in Publisher then it doesnt replicate to Subscriber unless it is done explicitly. I am looking for a way to track or monitor this. I am not able to find any system ...
1
vote
2answers
53 views

INSERT or UPDATE the values from a table A into the columns of table B based on table A's value in its row?

I have a table which contains attribute names with there values but each row is a attribute with its value, ItemId ItemAttributeName ItemAtributeValue 405 Application High AmpacityConnections 405 ...
1
vote
2answers
89 views

Understanding the floor performance of a SQL Server snapshot restore

I'm currently working on understanding the performance of snapshot restores in my company's test suite. I've read a lot of material online about how the time it takes to perform a restore is directly ...
0
votes
1answer
49 views

What does this statement mean: SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-‌- Logins To Be Created --]

What does this statement mean? SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --] It is part of a script I'm trying to adapt for listing out the SQL Server logins in an ...
0
votes
1answer
28 views

Help in querying hash PK and FK

I was trying to get the name of the PK and FK. However, this is the output that I get: PK: PK__adm_lib___3214EC067E4F7CCA FK: FK__adm_lib_S__fk_Co__7093AB15

1
2 3 4 5
57