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
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] ...

1
2 3 4 5
54