Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,843
questions
-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
