Tag: tsql

Why SP gives slightly different result than SELECT statement with same filters

Cant understand why Stored Procedure gives slightly different result than SELECT statement. In the SP I filter my data in #TempTable then using EXISTS statement. And in my SELECT query same values in WHERE clause. I tried one by one parameters and only SICCode effecting the result. Here is my SP: ALTER PROCEDURE [dbo].[EarningPlazaCommercial] @State […]

Why is an SQL update on a table variable slower than with a temp table

I have something like: DECLARE @tbl TABLE ( name varchar(255), type int ) UPDATE c SET c.name = t.name FROM dbo.cars c JOIN @tbl t ON t.type = c.type I have a stored procedure that does something similar but it takes over 20 minutes with the table variable. It runs in less than 2 minutes […]

Metric for prioritising SQL SERVER dm_exec_procedure_stats

This query provides A LOT of useful information, but I need to come up with a good metric for prioritising the data. Can anyone come up with an ideal sequence for the ORDER BY clause? I’m guessing the key fields for calculating the priority order will be [execution_count], [total_worker_time], [total_physical_reads] and [total_logical_writes] SELECT DB_NAME(database_id) databaes_name […]

Visual Studio SQL Server 2016 database project and Temporal Tables

Is there a way to enable temporal tables in a Visual Studio SQL Server 2016 database project? I cannot find a way to get this to work. I guess it could be a compatibility issue, the only way I’ve found is to enable the table in the post deployment script, but then on the next […]

Duplicate records in a select statement except for one column in complex join

So I have a query that would select data metrics that has long joins and aggregates. Some of the results would return what seems to be a duplicate except for one column, and that is a transaction number. Reason for this is that a separate transaction was made for the exact same record. We can’t […]

Logon Trigger for auditing

Using SQL Server 2008R2 I am working on a LOGON trigger to capture users who have logged in. I have created a trigger, but it is capturing the SQL Agent account and is inserting it like mad. I would like to exclude it from the list, but I’m not sure how to modify the trigger […]

Insert into 2 tables with cursor, then use returned scope_identity to insert into another table

Basically I want to use an existing table, lets call it T1. I have to take that table, row by row and insert different columns into 2 separate tables. For example, C1, C2 into T2 and C3,C4 into T3. During both of these inserts I need to make sure that the values that I am […]

Using dynamic DB name in a view

Create view xxx as Select * from DBName.dbo.table1 I have an above mentioned view where DBName is dynamic. How can I create this view? I know we can’t use dynamic variables in a view. I wanted to know how it can be done with the help of a function.

ROUND T-SQL of values 0.0

I am working on a Select statement where I have to show the average of genres but the result must show the 0 values as 0.0 My code is SELECT genreName AS ‘Genre’ , CAST(CASE WHEN AVG(rateValue) IS NULL THEN 0 ELSE ROUND(FORMAT(AVG(rateValue),2),0.0) END AS FLOAT) AS ‘Average Rating’ FROM [IMDB].[FilmGenre] flmgnr LEFT JOIN [IMDB].[FilmGenreAllocation] […]

Delete all files in child tables

I have the following SQL tables: create table dbo.Media ( Id int identity not null constraint PK_Media_Id primary key clustered (Id), Created datetime not null, ) create table dbo.MediaFile ( MediaId int not null, FileId int not null ) create table dbo.[File] ( Id int identity not null constraint PK_File_Id primary key clustered (Id), Content […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.