The difference between Union and Union all is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. A UNION statement effectively does a SELECT DISTINCT on the results set. If you select Distinct from Union All result … Read more
Using SetTriggerOrder is fine, but if your code depends on a specific sequence of execution, why not wrap all your triggers into stored procedures, and have the first one call the second, the second call the third, etc. Then you simply have the first one execute in the trigger. Someone in the future will be … Read more
without Dynamic SQL each option it’s clause for example: ORDER BY case when @var1 = ‘priority asc’ THEN priority END ASC , case when @var1 = ‘priority desc’ then priority end DESC, case when @var2 = ‘report_date asc’ then report_date end ASC, case when @var2 = ‘report_date desc’ then report_date end DESC
With the table valued function you generally use Cross Apply. Select * From myTable m CROSS APPLY fn_function(m.field1, m.field2)
You don’t need a CASE expression Just leverage how bit works: all non-zero values give 1 when cast to bit SELECT u.Hostname, u.IsCustom, ~ CAST((u.Status = 5) AS bit) AS IsActive from SiteUrlMappings u
In my opinion, I think it’s important to explain the why behind the need for a GROUP BY in your SQL when summing with OVER() clause and why you are getting repeated lines of data when you are expecting one row per BrandID. Take this example: You need to aggregate the total sale price of … Read more
You could first do a self-join on the exchange rates which are ordered by date so that you have the start and the end date of each exchange rate, without any overlap or gap in the dates (maybe add that as view to your database – in my case I’m just using a common table … Read more
Try Case SELECT stock.name, CASE WHEN stock.quantity <20 THEN ‘Buy urgent’ ELSE ‘There is enough’ END FROM stock
This expression 12-4-2005 is a calculated int and the value is -1997. You should do like this instead ‘2005-04-12’ with the ‘ before and after.
You could try something like this. SELECT A.Val AS A, B.Val AS B, C.Val AS C FROM MYTABLE cross apply(select 1 + 2) as A(Val) cross apply(select A.Val + 3) as B(Val) cross apply(select B.Val * 7) as C(Val)