Set up valuable Indexes on a table
I have a really big transaction table, billions of rows, and I need some kinds of Indexes to make it useful. Right now there is a clustred columnstore index on the table, and a nonclustred on ID, but I guess I need some nonclustred to make the table…
SQL Server Non Clustered Index CREATE taking forever to cancel
I ran a CREATE NON CLUSTERED INDEX command for 1.5 hours. Decided to cancel it because it was stopping other things running. The command has now been running for 22 hours. What is it doing that takes so long to roll back? The table is 2 billion rows and…
delimited split gives null for some items
hi all, I am using a function to split a string into multiple rows. The function is available here: https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function What I am trying to figure out is how to…
Why does impersonation (Execute as) work in a User database and not in the master database in Azure SQL DB?
Execute As User = 'User1' doesn't work in the Azure SQL DB master database. User1 account created in the master database and in a user database. Execute As User works in the user database and in the master database, receives the error: Msg 15517, Level…
Help with SQL
Hi Experts, Need help in creating calculated columns as below. My Data: Expected Result of two calculated columns: Calculated Column 1 = Previous calculated value in Calculated Column 1 * Rate Calculated Column 2 = RecentMinRent * current…
COALESCE and CONCAT Address Line 1 and Address Line 2 separated by a comma
We are trying to COALESCE and CONCAT Address Line 1 and Address Line 2 separated by a comma "," from potentially two data sources. So like...123 Main St., Apt. 101 The first part is easy enough... CONCAT ( COALESCE ( LTRIM (RTRIM…
Parsing First, Last, Middle Initial and suffix from full name
I am trying to extract first last middle initial and suffix from a name field and there's no set pattern please see the pattern of the in my table LOPEZ ROQUE, CARMELINA ORTEGA, GISELLE A A RUSSELL JR, WILLIE C NUNEZ, LILIANA I have looked online and…
How can I resolve the "could not find server" error when using execute(@sql) at [linked_server] with a stored procedure?
I have created a stored procedure in SQL Server to execute a PL/SQL script to a linked Oracle server using either sp_executesql or execute(@sql). The former method works while the latter generates a "cannot find linked server" or…
When using execute(@sql) at [linked_server] I get "Could not find server" error
I have written the following stored procedure to execute a PL/SQL script to a linked Oracle server: ALTER PROCEDURE [dbo].[Oracle_s] ( @sql varchar(4000) ) AS BEGIN declare @result bit = 0; set nocount on; declare @temp nvarchar(4000) = 'SELECT *…
Text delimiter file
Hi, I have two tables: one for headers with 9 fields and another for lines with 6 fields. Both header and lines are highlited in yellow. I need to combine both the headers and lines using a UNION operation to display the result below. From the line…
sql query where with condition
Hi, I have a query same but only one of the where clauses differ. If (@accountID = 100) Begin select field1, field2 FROM table1 left outer join table 2 where status = 'x' and ISNULL(accountid, @accountID) = @accountID END Else select field1,…
How to Calculate salary of employee
I am calculating salary using below query SELECT '29' as Total_Days,'28' as Present_Days, (14000 / 29) AS Per_Day, (14000 / 29) * 28 AS Total_Salary; But Total_Salary should be 14000 because Total_Days 29 and Present_Day 29
Retrieve value from the row above
How can I retrieve the FinalBalance value from the previous row and add it to the Total value in the current row, to get the new FinalBalance value for each row? The table structure and desired outcome are shown in the image below. Each row's TransDate…
SQL Server optional parameter performance issue
Hello guys, I have an optional parameter in a SP e.g. @MyParam int = NULL When I check the param against NULL upfront I end up with TWO queries e.g. IF (@MyParam IS NULL) BEGIN SELECT ... END ELSE BEGIN SELECT ... END Otherwise if I check…
best approach to migrating a table from system to another
I have a test system where we update entries on a table for cross refence purpose and was wondering the best approach for this.
cleanup old transactional replication snapshot files
I have a transactional replication between a publisher and a subscriber where the subscriber also acts as the distributor. I am facing storage issues as old snapshot files are not being auto cleaned up after successful application via distributor. I…
VB.NET SQL Connection and SQL Command Error BC3002
Hello, I'm in the process of converting VBA to VB.NET with SQL statements. One of the changes is utilizing the "ExecuteNonQuery". Since I'm still learning the syntax, I simply copied the VB.NET statements from a web-site. But I'm getting 2…
should i prefer join over exists or in
hi, I have seen people use exists when exists is required and no cols are required in select. But I notice sometimes exists slows down and join performs better q1) is there any news i should know , that one should not use exists or in , and should go…
What Roles give SHOWPLAN permission?
With certain server level or database level roles, you have SHOWPLAN permission. Is there a hierarchy list that would indicate all the permissions inherited by various roles? I'm thinking of assigning ##MS_ServerStateReader## to our developer Login on…
Connect to on-prem sql server using Azure Relay
Hi, I would like to connect to a on-prem sql server and execute T-SQL commands to listen and receive SQL broker messages from Azure cloud hosted .net application. How can i connect to on-prem sql server and execute these sql commands and receive the…