4,600 questions with Transact-SQL-related tags

Sort by: Updated
Updated Created Answers
2 answers One of the answers was accepted by the question author.

Query to get Month , Day and Week number of current month.

Hi, Need a query to get Month , Day and Week number of current month.   Sample table   DateMonth NameDayWeekYear6/29/2024JunSatWK52024

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-29T10:28:08.37+00:00
mrrobottelg 20 Reputation points
accepted 2024-07-01T06:06:20.8933333+00:00
mrrobottelg 20 Reputation points
5 answers One of the answers was accepted by the question author.

How to calculate Median Value

Suppose i have table and one column has numeric value. now how could i calculate median value ? i found one code from this url https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server but their approach is not…

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2020-12-13T19:32:57.437+00:00
Sudip Bhatt 2,276 Reputation points
answered 2024-06-28T14:04:48.3766667+00:00
Cédric Hutt 0 Reputation points
2 answers

Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259.

DECLARE @RowId NVARCHAR(255) DECLARE @Query NVARCHAR(MAX) DECLARE @Header NVARCHAR(MAX) SET @RowId = '[sep=,' + CHAR(13) + CHAR(10) + 'RowId]' SET @Query = 'set nocount on; SELECT ROW_NUMBER() OVER (ORDER BY PDTMO.MONo DESC, PDTWF.CreateDate ASC) AS ' +…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-28T05:37:28.5766667+00:00
Elson Wong 0 Reputation points
commented 2024-06-28T05:38:54.6066667+00:00
Elson Wong 0 Reputation points
2 answers One of the answers was accepted by the question author.

Insert with Transaction Significantly Faster than Individual Inserts - SQL Server

Hello, Could you please explain this? Why insert with Transaction significantly faster than individual inserts in SQL Server?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2021-12-06T14:26:13.723+00:00
Shervan360 1,481 Reputation points
commented 2024-06-27T11:21:02.9166667+00:00
Victor Almeida Schinaider 0 Reputation points
1 answer One of the answers was accepted by the question author.

Sql join query with total row

Hi , please help create table #temp (LedgerID INT, Amount Decimal(18,3)) insert into #temp (LedgerID,Amount) values (121,100),(121,250),(129,25),(129,200),(158,100),(158,500),(157,250),(157,100),(48625,1000) CREATE TABLE #temp_table ( cid INT, type…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-24T07:11:57.31+00:00
Mohamed Farook 161 Reputation points
accepted 2024-06-27T04:39:03.08+00:00
Mohamed Farook 161 Reputation points
2 answers One of the answers was accepted by the question author.

SP execution time varies with different environment

Dev environment is restored with the latest Test. When a SP with same set of parameter is executed against Test as well as Dev, the execution time is differing with both the environments. In Test it takes 1.45 mins, whereas in Dev it is 3.16/3.08…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-22T12:51:19.8666667+00:00
Radhai Krish 191 Reputation points
accepted 2024-06-27T04:36:38.89+00:00
Radhai Krish 191 Reputation points
1 answer

SQL : Stored Procedure variables if returning 0 or null values do not run next steps

Hi Team - If any of the 4 variables are returning 0 or NULL value do not run the next insert scripts. Could you please help how i can write a condition. TIA Create PROCEDURE [FIN].[Test] @PeriodToAllocate varchar(8) /* */ AS SET NOCOUNT ON DECLARE…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-24T03:21:31.3766667+00:00
kkran 831 Reputation points
answered 2024-06-24T07:21:59.4866667+00:00
LiHongMSFT-4306 25,651 Reputation points
2 answers One of the answers was accepted by the question author.

convert returns conversion error but try_convert does return the correct value

Hi All, I have the following case statement in a TSQL script, which returns the correct negative decimal value, in the rows that acually have a value try_convert (decimal (18,6), max (case when [databody.daysQuality.testResults.testType] =…

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-17T04:41:27.08+00:00
hans smeets 20 Reputation points
accepted 2024-06-23T20:56:02.5533333+00:00
hans smeets 20 Reputation points
2 answers One of the answers was accepted by the question author.

Syntax - How to use variable in LIKE clause

Trying to use a variable in the LIKE clause of a SELECT statement. Hope this is simple, but available guidance hasn't worked. As seen below, query works when a "fixed" value is used with wildcard characters, but I haven't managed to get it with…

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-20T16:18:26.5933333+00:00
TreyS 166 Reputation points
accepted 2024-06-21T16:24:26.58+00:00
TreyS 166 Reputation points
2 answers One of the answers was accepted by the question author.

elapsed_time value more than query execution time?

I did take query from here in order to check performance of our queries. Executed a SP which took execution time of 1 min 45 seconds. Ran the query from the link which uses the view 'sys.dm_exec_query_stats' Highlighted rows in the screenshots are…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-20T11:09:53.1166667+00:00
Radhai Krish 191 Reputation points
accepted 2024-06-21T05:24:01.7233333+00:00
Radhai Krish 191 Reputation points
2 answers

ID's coming back as nulls are not appearing in my data set

I am trying to create a, intergrity check that bring back data after a record has been cancelled after a certain event. In my query. I am joining to a few tables and I can see all the old cancelled record. When I then cancel any new records, some IDs I…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
67 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-19T13:29:32.62+00:00
Kuda 20 Reputation points
answered 2024-06-20T01:25:23.59+00:00
Bruce (SqlWork.com) 61,491 Reputation points
2 answers One of the answers was accepted by the question author.

Unexpected truncation error when inserting from UTF8 to non-UTF8 collation

I believe I am hitting a bug with moving data from a UTF8 to a non-UTF8 collations in SQL Server 2022 CU13 (running under Linux but assuming for now this issue is not related). The issue is that MSSQL appears to be determining the width of a string for…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-11T00:42:42.9533333+00:00
Ben 30 Reputation points
commented 2024-06-18T21:02:02.6166667+00:00
Erland Sommarskog 107K Reputation points
3 answers One of the answers was accepted by the question author.

“user_scans” of table “sys.dm_db_index_usage_stats”

I am trying to find answer to the below. What exactly the scan means for the field "user_scans" being with the table "sys.dm_db_index_usage_stats" ? Like, there are tables in our prod which aren't used for sure as the module is…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-17T05:33:15.94+00:00
Radhai Krish 191 Reputation points
accepted 2024-06-18T06:14:28.97+00:00
Radhai Krish 191 Reputation points
4 answers

SSMS freezes on student computer when I demostrate TSQL queries using MS Teams on my computer

I lecture Microsoft SQL courses from my home using Microsoft Teams. I installed the latest edition of SSMS & SQL Dev 2019 Ed on my Lenovo laptop. During the lecture I would share my query window with the delegate to demonstrate TSQL queries. The…

Microsoft Teams
Microsoft Teams
A Microsoft customizable chat-based workspace.
9,611 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Windows 11
Windows 11
A Microsoft operating system designed for productivity, creativity, and ease of use.
8,974 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2023-10-18T21:24:44.0066667+00:00
Marais Viljoen 10 Reputation points
answered 2024-06-17T14:49:01.7833333+00:00
Geoff 0 Reputation points
2 answers One of the answers was accepted by the question author.

Get Global Primary details from All the replicas in Distributed AlwaysOn

In Distributed AlwaysOn, I could not find any DMVs in SQL Server which could provide the details about Global Primary from replicas in Forwarder side (both Primary Forwarder and its local replicas). Is there a way to at-least pull the Global Primary…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-10T14:06:34.08+00:00
Sajal Bagchi 25 Reputation points
accepted 2024-06-17T13:39:37.6566667+00:00
Sajal Bagchi 25 Reputation points
1 answer One of the answers was accepted by the question author.

Querying table index stats not working

I am trying to query my tables index fragmentation in Azure Synapse studio with query: SELECT IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, …

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,687 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,180 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-14T09:17:01.14+00:00
Jani Hämäläinen 40 Reputation points
accepted 2024-06-17T10:19:38.69+00:00
Jani Hämäläinen 40 Reputation points
3 answers One of the answers was accepted by the question author.

Partition by filter

Hey Team I have table where A values comes then E value should be filter or else E value should be there partition by data and mpr column expected values

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
67 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-14T14:34:28.8633333+00:00
Vineet S 425 Reputation points
edited an answer 2024-06-14T21:11:05.9233333+00:00
Naomi Nosonovsky 525 Reputation points
2 answers One of the answers was accepted by the question author.

transpose row data into columns

Hope I'm asking this correctly. I have a table in this format: I need to have the Unique ID and the account numbers from both stores all in the same record. Like this: How can I accomplish this in SQL? Is this some kind of PIVOT or UNPIVOT? What…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-08T14:01:19.5733333+00:00
Dom 771 Reputation points
commented 2024-06-13T16:44:02.6533333+00:00
Dom 771 Reputation points
2 answers

WHY Index Scan Instead Of Seek While Joining Big Table With #Temp Table

In my sql server 2016 database, I have a table 'BigData' having around 50 million rows and a Temporary table #TempID which contains 600 rows. CREATE TABLE BigData (ID INT IDENTITY(1,1) PRIMARY KEY, OtherID INT, Comment nvarchar(max),…

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,347 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-10T11:46:44.9033333+00:00
Vikrant Sirohi 0 Reputation points
edited a comment 2024-06-11T03:45:37.8833333+00:00
Vikrant Sirohi 0 Reputation points
1 answer

Syntax Error When Attempting to run PREDICT function against table in dedicated pool in Synapse Studio

I am attempting to complete the tutorial listed here : https://learn.microsoft.com/en-us/azure/synapse-analytics/machine-learning/tutorial-sql-pool-model-scoring-wizard I am able to complete all of steps until I get to the final step, where I use Synapse…

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,687 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
asked 2024-06-05T22:27:20.54+00:00
Andy 0 Reputation points Microsoft Employee
answered 2024-06-06T10:41:02.9033333+00:00
phemanth 8,645 Reputation points Microsoft Vendor