Questions tagged [t-sql]
Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
2,666
questions
0
votes
1answer
43 views
Benefits of not having a clustered index on tables (Heaps)
What are the benefits of not having a clustered index on a table in SQL server. Will a
SELECT * INTO TABLE_A FROM TABLE_B
Be faster if TABLE_A is a heap? Which operation will benefit if the table is ...
0
votes
3answers
64 views
What is the cost of having many columns as included columns in a nonclustred index?
What is the cost of having many columns as included columns in a nonclustred index in SQL Server?
0
votes
1answer
25 views
Insert in to table without duplicate records TSQL
Team,
Need some assistance with TSQL
table1
ID |Name| pin
1 a 101
2 b 201
3 c 301
4 d 401
table 2
ID|Name|pin
3 c 301
4 d 401
5 e 501
6 f 601
I want to ...
1
vote
1answer
75 views
Eliminate filter operator before columnstore index scan operator
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,...
-4
votes
1answer
20 views
number weeks from April to September for every year [closed]
I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following ...
1
vote
0answers
58 views
Linked Server throws metadata error
I have a function in SQL Server 2019 that copies data from a local table to an Oracle 11.2.0.4 table via Linked Server, using Oracle 19.3 client. Recently, a new column was added to the Oracle table, ...
0
votes
0answers
29 views
Error - he given value of type Decimal from the data source cannot be converted to type bigint of the specified target column
I am very new to T-SQL. I have a query which fetches Some Always-on DB Info. I have created a table in a central server and using PowerShell I will query the AG Servers and load the data into the ...
1
vote
2answers
39 views
How to update group no incremental by 1 based on every group from 1 to 3?
I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3
meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc ..
create table #...
-1
votes
2answers
50 views
Can an expression be used as an expression as well as a predicate? [closed]
In MySQL, Oracle and PostgreSQL an expresion like 1 = 1 can be used in where like:
where 1 = 1 as well as
where (1 = 1) is not null
In MSSQL, it seem that:
a) when an expression is a predicate, it ...
1
vote
1answer
51 views
Clustered and columnstore indexes
I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So ...
-2
votes
2answers
52 views
TSQL - Sql Injection
DECLARE @TSQL NVARCHAR(MAX) = N'SELECT 1, ''One''';
CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10));
INSERT INTO #ThisIsATable
EXEC [dbo].[sp_executesql] @TSQL ;
Is it safe to have input ...
0
votes
1answer
49 views
Getting the “Column name or number of supplied values does not match table definition” when I use CTEs in an IF statement inside a stored procedure
I have a Stored Procedure that has 3 parameters. The stored procedure looks like this:
ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '')
AS
SET NOCOUNT ON
DECLARE @...
0
votes
1answer
82 views
Improving Speed of Cross Apply
I posted a question on how to create a relation between 2 tables based on a spatial field
https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering
The performance is ...
0
votes
0answers
23 views
TSQL Join on Nearest (tables have no relationships)
I have 4 tables
Listings
Cities
Regiions
Countries
Listing is not related to others at all, but cities, regions and countries have FK.
I would like to get a list of Listings but also include in the ...
0
votes
1answer
27 views
Get count of near points based on 2 unrelated tables
I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on.
They both have a geography field.
I would like to get list of all ...
-1
votes
1answer
45 views
Log backup full [duplicate]
Please I got this error message when trying to delete some rows a table on my database :
message 9002, level 17, state 2, line 48 The transaction log for
database 'NewHopeHospial' is full due to '...
0
votes
2answers
56 views
T-SQL query must include and exclude child rows
I have simple parent -> child (one to many) relationship. Need to return children that have all given attributes, in this case red hair and brown eyes.
Table:
CREATE TABLE [dbo].[children](
[...
4
votes
2answers
167 views
SQL Server not using nonclustered index
I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (...
2
votes
1answer
52 views
SQL: pivot non numeric data [duplicate]
I have a set of data which basically looks like the following:
+---------+--------+
| EventId | Field |
+---------+--------+
| 1 | apple |
| 1 | paper |
| 1 | boy |
| 2 | ...
2
votes
1answer
98 views
What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?
My company logs DDL on one database using a Database Trigger, looking for DDL including:
ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
When I disable or enable triggers using ALTER TABLE x ...
4
votes
1answer
259 views
Add query hints to a CROSS APPLY [duplicate]
I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *
FROM sys.objects obj WITH (NOLOCK)
...
1
vote
1answer
24 views
TSQL success or failure result of EXEC dynamic SQL
Based on the following code:
/* This SP takes a query and a unique temporary table name as input...
The outputs is a string with the command to create the tmp table based on the query that is given ...
0
votes
2answers
24 views
JOIN between 3 tables to show missing data from 2 tables
I have 3 DB tables, a Computer table, a Patch table, and a ComputerPatched table. Below is a table mock up to assist my explanation....
DECLARE @Computer AS TABLE (
[ComputerID] int
, [...
0
votes
1answer
40 views
T-SQL Query to retrieve the last full backup (Including split and copy only), Size and Location
I am using the following query answered by Marcello Miorelli. This query serves the purpose for normal backups but fails to determine the actual backup size of all the split backups and copy only ...
5
votes
2answers
158 views
CTE query that fails to find ancestors
I have a table foo
with the content
So the records of the table foo can graphically be represented with this graph.
When I run a procedure that should return ancestors using @starting_id=9
(...
1
vote
1answer
43 views
TSQL trigger on actual changes - update field
Background -
Table that looks like this:
country_id
product_id
flow_date
update_date
amount
country_id, product_id, flow_date, and update_date are a composite PK, because I need to track the history ...
1
vote
1answer
45 views
Select statement take more time in sql
Statement 1 : select top 1000 pmch.Columnname1
Statement 2 : select top 1000 pmch.*
Statement 2 executes quicker the Statement 1.
I know that using "SELECT * " will affect performance so ...
-3
votes
1answer
42 views
Best way to update a Table
I have a SP that updates rows in a table periodically (several times a day) the SP currently gets a feed of the PK and the update value. The input feed can contain 1 - 1000 rows.
I want to know if it ...
0
votes
0answers
49 views
Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module
DBAs,
Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over?
When I hit debug (Alt-F5), gives me this error, after making some ...
1
vote
2answers
76 views
SQL Server always running
I have a personal computer where I have different instances of SQL server running (developer edition) with both integration services and analysis services (one in tabular and one in multidimensional). ...
2
votes
2answers
54 views
Recursively get ids of tree
I have a table foo.
So we have a hiearchical object structure where on foo has one parent (or none) and a list of children.
What I want is a sql statement that lists all id:s that can be found either ...
-1
votes
1answer
65 views
SQL AVG() Returning the wrong result [closed]
I have a function and a stored procedure that used this function, when i run the function seratelty with the stored procedure i get the correct result but when i use this function with the stored ...
-1
votes
1answer
33 views
Complex SQL Agent Job
I would like to tun the Stored Procedures on the following dates.
1st sept 2020
1st Nov 2020
1st Dec 2020
1st Feb 2021
1st March 2021
1st May 2021
1st june 2021
1st august ...
2
votes
1answer
48 views
Space Calculation for SQL Server Table: What's wrong with my query?
I'm using the following query in order to calculate some space related measurements on a particular table in my SQL Server database:
SELECT
t.name AS TableName,
p.rows,
(sum(a....
2
votes
1answer
25 views
Validating SQL Server Change Tracking Synchronization
I have some tables that I need to synchronize from one SQL Server database to another. I'm using SQL Server's "Change Tracking" feature. My program uses Change Tracking to synchronize the ...
0
votes
1answer
35 views
SQL Server :: how to move country code column to another table?
I have 2 tables:
tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG).
Like this:
CountryName TwoCharCountryCode ...
-1
votes
1answer
27 views
TSQL query searching for multiple keywords across multiple fields
I am looking for assistance in improving the search capability of my webstore. I have tried multiple methods without success. I sell diecast cars, and am missing out opportunities of products being ...
2
votes
3answers
52 views
Query to find sql server services currently running [duplicate]
Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?
0
votes
1answer
31 views
Joining Additional Table Column
I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to ...
2
votes
1answer
51 views
Populating bridge table with conditions [closed]
I have a bridge table trajectory_detail that I am supposed to populate with millions of records from the corresponding parent tables. The problem is that every value from parent table trajectory_id is ...
0
votes
3answers
47 views
Simultaneous SQL full backups of newly created databases
I would like to take a backup of newly created databases but within a minute if the user creates three databases the script can able to take only one database but for one new database which was ...
0
votes
1answer
18 views
OPENROWSET Bulk insert Text File NULL Columns
I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
0
votes
1answer
23 views
Query specific key fields from x-number of tables?
I'm building an insurance database and I need some help with best practice on fast quering data.
Let's say I have three insurance types ... Car, Home and Travel.
Common for all three types, I have the ...
1
vote
1answer
33 views
Execute SSIS (package) in SSMS using T-SQL and return a value
Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I ...
-1
votes
3answers
21 views
Set The Order Query Results Display
I need to display my emailType in DESC order. Now it is a varchar field so I need it to be reverse alphabet order Z - A. When I run my query it is displayed in ASC order, and since I am using a ...
2
votes
2answers
75 views
Optimizing my query
Here's my query:
DECLARE @monthStartDate date;
DECLARE @monthEndDate date;
SET @monthStartDate = DATEFROMPARTS(2020,6,1);
SET @monthEndDate = EOMONTH(@monthStartDate);
SELECT @...
0
votes
0answers
43 views
Index usage for database: where are the sys tables located
I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
0
votes
0answers
9 views
No Data for RING_BUFFER_SCHEDULER_MONITOR
I am attempting to add a query for monitor recent CPU usage for our SQL servers into an SSRS dashboard. I have encountered numerous examples online recommending querying the sys.dm_os_ring_buffers DMV,...
0
votes
1answer
24 views
After Insert Trigger WIth MS SQL Server
I think I have all my bases covered but wanted to ask someone who knows better than I. This is my syntax.
UPDATE SI
SET autoInsert = 0
FROM inserted
WHERE SI.autoInsert = SI.autoInsert
OR SI....
-1
votes
1answer
48 views
How do you convert a cursor to CTE? T-SQL
In an effort to clarify I have added more information and removed the "simplified" code.
I have table with datetime punch information.
CREATE TABLE [dbo].[AT_Punches](
[PunchID] [int] ...
