SQL Server – Function call in WHERE condition

I’ve a question regarding making a function call in WHERE condition of a SELECT statement in my stored procedure. Here is my sample query that mimics my original. I’ve 3 million records in the production database for primary table CDP. My concern is if there would be any performance impact of using the function testFunction(@UserId) […]

How do I add my order by field to this select statement?

To avoid “ORDER BY items must appear in the select list if SELECT DISTINCT is specified.”, I need to add AssessmentSuperSet.Deadline to my SELECT statement at the top. Where should I do this? Any help greatly appreciated. Thanks. DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF( (SELECT distinct ‘,’ + QUOTENAME(AssessmentSuperSet.Title) from […]

How to speed up import of .sql dump file in to AWS RDS instance?

I’m trying to import a 55 meg .sql file in to an AWS RDS instance. The dump file was generated with this command: mysqldump -u root -ppassword dbname > dbname.sql I’m running the import with this command: mysql -u root -ppassword –host=x.rds.amazonaws.com dbname < dbname.sql The import was running for about 20 minutes, so I […]

Select Into #Temp From Select Query

I have a code that looks like this Select * Into #DTemp From (Select A, B, C, D From T1 LEFT OUTER JOIN T2 ON T1.I=T2.I LEFT OUTER JOIN T3 ON T2.I = T3.I …. T6 ON T5.I = T6.I WHERE A = ‘THE’ AND B LIKE ‘%YYYY% AND C >= (TIME) ) But I […]

Big difference in stored procedure execution time from Node and in SQL Server Management Studio

My procedure ran from Node.js (Azure Mobile Services) tooks 5.7 seconds (time between start of success callback and sending a query). The same procedure ran with the same parameters from SSMS took 0.4 second. How can I debug this problem? My ideas of problem source are: bad execution plans bad connection settings for Node.js (probably […]

Delete from two related tables by useng ID returned from the first one

I want to delete 2 records from related tables(the record with the same id of foreign key) by using sqlcommand. Is there any method like SCOPE_IDENTITY that return the last ID has been deleted? I don’t want to use ON DELETE CASCADE Constraint. Any idea? Thanks.

How to take 2 datetimes and get the hours per each date within the two datetimes?

I have the following data in a database: Job ClockInDateTime ClockOutDateTime MM00151509 2013-11-19 07:01 2013-11-19 09:20 MM00151800 2013-11-09 09:08 2013-11-20 11:36 MM00153591 2013-12-01 08:20 2013-12-03 08:15 MM00154121 2013-12-05 08:19 2013-12-05 10:32 I would like to take each row and show the the date along with the associated hours for that date. As you can see […]

How to change sql server license in Azure VM?

We currently have a VM in Azure which was created from the Azure gallery as a VM with Sql Server Enterprise licensing included on a per-hour basis. This is a very expensive VM. We have MSDN licenses, and this is a dev machine, so we want to change the licensing. How can we change the […]

Joining two tables using a Subquery in the FROM Clause

I’m losing my mind on this question I have on my homework. NO I DON’T WANT YOU TO GIVE ME THE ANSWER! I just want to be pushed into the right direction, because I’ve been struggling with this. Here is the question. Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order […]

updating multiple columns using case statement in sql server

I would like to update the table using case statement the query is like this… select case(@columnname) when ‘name1’ then begin update table set pay1=pay1* 100 pay2=pay2*20 pay3=pay3* 100 end when ‘name2’ then begin update table set pay2=pay2*20 pay3=pay3* 100 end when ‘name3’ then begin update table set pay3=pay3* 100 end end can u please […]

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