You’re right: at the standard isolation level, read committed, you do not need to wrap select statements in transactions. Select statements will be protected from dirty reads whether you wrap them in a transaction or not. connection 1: connection 2: begin transaction update user set name=”Bill” where id = 1 select name from users where … Read more
& is the bitwise logical and operator – It performs the operation on 2 integer values. WHERE (sc.Attributes & 1) = 0 The above code checks to see if sc.Attributes is an even number. Which is the same as saying that the first bit is not set. Because of the name of the column though: … Read more
If you’re using a table variable, you can’t do it. If it were a table, you could truncate it or use DBCC CHECKIDENT. But, if you have to use a table variable, you have to use something other than an identity column. Or, more accurately, use the identity column in your table variable but output … Read more
Use OUTER APPLY. Also I’m not sure if the ON clause after the OUTER APPLY is really needed. If the invoiceid is the same coming out as going in then probably not. Select iv.invoiceno, iv.invoiceitem,iv.invoiceno from invoice iv inner join deal d on d.dealid=iv.dealid inner join invoiceitemview ivt on iv.invoiceid=ivt.invoiceid and iv.invoiceno=ivt.invoiceno inner join warehouse … Read more
You should read this: http://www.sommarskog.se/error-handling-I.html I can’t recommend that link highly enough. It’s a bit long, but in a good way. There’s a disclaimer at the front that it was originally written for SQL Server 2000, but it covers the new try/catch error handling abilities in SQL Server 2005+ as well.
My init understanding is that a Select query won’t lock a table, or won’t cause a deadlock This understanding is wrong. SELECT queries take shared locks on the rows they analyze. Shared locks may conflict exclusive locks from update/delete/insert statements. Two SELECT statements are not going to deadlock, but a SELECT can deadlock with an … Read more
SELECT CAST(‘abc’ AS varchar(5)) COLLATE French_CS_AS
You can use: Select count(created_date) as counted_leads, created_date as count_date from table group by created_date
You can’t update more that one table in a single statement, however the error message you get is because of the aliases, you could try this : BEGIN TRANSACTION update A set A.ORG_NAME = @ORG_NAME from table1 A inner join table2 B on B.ORG_ID = A.ORG_ID and A.ORG_ID = @ORG_ID update B set B.REF_NAME = … Read more
A restore can only happen if the database does not have any connections to it (besides yours). The easy way on a MS SQL Server to kick all users off is: ALTER DATABASE [MyDB] SET Single_User WITH Rollback Immediate GO Now, you can perform your restore with impunity. Make sure you set it back to … Read more