Loading...
|
|
|
|
|
|
|
|
| Question of the Day |
| Today's question (by Steve Jones - SSC Editor): | |
| Percentile Memory Grant Feedback | |
| Percentile Memory Grant feedback was introduced in SQL Server 2022 as a way to adjust the memory grant feedback not to the last execution, but as a percentile of history. What compatibility levels does this work under? | |
| Think you know the answer? Click here, and find out if you are right. | |
| Yesterday's Question of the Day (by Thomas Franz) |
| Swapping values of variables I have two variables and want to swap / exchange their values, what will be the result of the following statements DECLARE @i INT = 1 , @j INT = 2 -- swap variables SELECT @i = @j , @j = @i SELECT @i AS i, @j AS j
Answer: @i = 2, @j = 2 Explanation: It is counterintuitive, but even if you are using a single SELECT statemet, the assignments will be done one after the other, as if you would have written multiple SET statements. SQL Server first set @i (originally 1) to the value of @j (2) and then sets @j to the new value of @i (2 instead of the original 1), so you end up with a 2 in both variables. If you really want to swap the variables, you need a third variable as temporary storage: DECLARE @i INT = 1 , @j INT = 2 , @t INT -- swap variables SELECT @t = @i , @i = @j , @j = @t SELECT @i AS i, @j AS j -- returns 2 and 1BTW: the UPDATE statement is not prone to this behavior, there you can swap two columns without any problems in a single statement: CREATE TABLE #tmp (i INT, j INT) INSERT INTO #tmp (i, j) VALUES (1, 2) UPDATE #tmp SET i = j , j = i SELECT i, j -- returns 2, 1 FROM #tmp AS t DROP TABLE IF EXISTS #tmp |
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums. |
| SQL Server 2017 - Development |
| Where can I download SQL server 2017 Developer edition? - I am setting up a new laptop and I need SQL server 2017 developer so that it is the same version as prod https://9apps.ooo/ . However I can't find a download anywhere. Anyone have a source, preferably at MS. |
| SQL Server 2016 - Development and T-SQL |
| Can SSIS Check for Viruses? - Apologies if it is a silly question. We periodically receive an Excel file from a customer. There is now a requirement to store it as a BLOB object in SQL Server for the reference. I would use Exchange Server for it: ask the customer to email the file to the Exchange Server and SSIS - […] |
| SQL Server 2019 - Administration |
| auto refresh a query result (F5) in SSMS - i have a job that load data into a table and a separate table that keep tracks of errors (when there's issues with the data loading). i have a query select this error table and display the columns as needed. instead of hitting F5 every 2-3 mins, can i automate this F5 to refresh the […] |
| SQL Server 2019 HA - Looking for a HA solution for SQL server for our branch offices. Current setup has our 3 offices running independent standalone servers with SQL server. No redundancy in place. My aim is to centralise the servers in 2 locations. Location A and B has one server each. Both running windows server vm and sql in […] |
| Looking for recompiling stored procedures - I have been tasked with the job of finding the top X number of stored procedures that are being recompiled regularly on a group of servers. I've tried several times to Google this and keep coming across "How To" articles on setting stored procedures for recompile, which is not what I want. I'm trying to […] |
| if then else issue - I'm trying to use the if then else to check if a member exist in a server role. if it does, do X, else do Y. but not getting the desire outcome. BEGIN DECLARE @member nvarchar(50); USE [master] SELECT @member = members.name FROM sys.server_role_members AS server_role_members INNER JOIN sys.server_principals AS roles ON server_role_members.role_principal_id = roles.principal_id […] |
| SQL Server 2019 - Development |
| Count Number of Records - Hi I am trying to write a SQL query that returns the number of working days between two dates. The approach I am taking is the following... Financial markets are open on non-holidays so doing a select distinct on trade dates would give me the list of all non-holiday dates. I could create a separate […] |
| Find the records based on group by Student ID - Data looks like below Status 1 is Active and 2 is Clean For one Student ID we may have one active, one clean status; I want to find the records only with Clean status for a student ID. Student Id Student_Status 12345 […] |
| problem of slowness in my requests - hello, I am having a major performance issue , with this part of code which is still blocked on my server , Attached is the part of the code that is causing the problem. DELETE R FROM #RESULTS_DEM AS R INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM WHERE ( NOT EXISTS ( SELECT […] |
| SQL Azure - Development |
| Copy DB from Prod to Test !! - Good morning. I would like your advice. I work on Azure Sql. We have 3 environments (Dev, Test and Prod) Each environment has a specific tenant. Every month, I would like to copy the database from Production to the Test environment. What solution do you recommend to perform this task? Regards |
| General |
| Partition in sql - I have the following data frame: df = pd.DataFrame({'A': [6,0, 4,2, 8, 2, 6,0, 4,8], 'B': range(0, 12), 'C': ['a', 'b','c', 'd', 'e','a', 'b','c', 'd', 'e']}) A B C 0 6 0 a 1 0 1 b 2 4 2 c 3 2 3 d 4 8 4 e --------------- partition by C 5 2 […] |
| cross join sql - I'm attempting to use INNER JOIN to join numerous tables. Here is the code: IF OBJECT_ID('tempdb..#tmpRecData') IS NOT NULL DROP TABLE #tmpRecData --STEP 1 SELECT DISTINCT pr.ChainID, pr.StoreID, pr.SupplierID, pr.ProductID, MAX(CAST(pr.ActiveLastDate AS date)) AS 'Active Date' --ChainID, SupplierID, StoreID, InvoiceDate, InvoiceNumber, SupplierInvoiceDate, SupplierInvoiceNumber INTO #tmpRecData FROM dbo.[ProductPrices_Retailer] AS pr LEFT JOIN ProductIdentifiers iden ON pr.ProductID […] |
| Analysis Services |
| how to get previous value in a fact table for a measure in mdx - Hello Guys! I want to write an mdx query with a calculated Measure. Here is my fact table: How to implement a mesaure with the rule below : If CurrentAtt != PreviousAtt then PreviousValue else CurrentValue I mean by previous the value in the previous period. Thank you so much! any suggestion please ? |
| Integration Services |
| I am having a issue with a SSIS Package - I have a big sql script that i am doing a flatfile destination on to create a csv. I have case statements and about 4 columns that I have used the FORMAT command on some dates, when I run the script in in SQL manager it all looks great and when i copy the script […] |
| SQL Server 2022 - Development |
| The place of SMALLDATETIME - SMALLDATETIME is a datatype that gets very little love online and few people recommend it. Kendra Little is right to warn about the rounding up/down of seconds. Microsoft explicitly recommends DATETIME2 over SMALLDATETIME on the Microsoft Learn page. However, it is only 4 bytes in length and gives accuracy to a minute and DATETIME2(0) is […] |
| |
| ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Loading...
Loading...
