In case someone is interested, i solved it by using SET IDENTITY_INSERT ON anyway, there was no other good way...

Something like this can be done, but as other mentioned, it's usually not a good idea!

IF OBJECT_ID('SP_TEST')...

DENSE_RANK should do it 😉

Or SELECT 'me' if you're a bit on the joking side...

Or:

SELECT MAX(salary)

FROM t

WHERE salary < (SELECT MAX(salary) FROM t)

Something like:

IF OBJECT_ID('INDEXED_CUSTOMER', N'V') IS NOT NULL

DROP VIEW INDEXED_CUSTOMER;

GO

CREATE VIEW dbo.INDEXED_CUSTOMER

WITH SCHEMABINDING

AS

SELECTcust_id, cust_no, zipcode, city, province -- etc

FROMdbo.customer

GO

CREATE UNIQUE CLUSTERED INDEX...

UPDATE t

SET col = CAST(col AS NUMERIC(19, 2))

FROM table_price t

?

Looks like your query won't compile since you join on non-existing v-table inside the LEFT JOIN?

Can you create an INDEXED VIEW inside the procedure that indexes the zip /state-columns?

That might...

Try MIN(ID) in the inner select and it will work i think?

Thanks, that's what i suspected, but it's hard to get how SQL Server semantics are in this case.

If you cast the number to numeric when building xml string you'll get rid of sc.notation

If you cast the number to numeric when building xml string you'll get rid of sc.notaion

Maybe you should take a look at Service Broker architecture if deadlocks are a big problem? All changes will be applied sequentially?

Alternatively try to understand why they occur.

For example if you have something like:

SELECT x, y FROM T WHERE A = 5

and index on column A, it might look like you're doing fine.

But if table has million...

It's not a bad idea to post the table definition, so it's possible to see what datatypes you select against and also how many rows you have in total and...

Well, you need to split the file by newlines and import content of each line into a staging table that looks something like:

CREATE TABLE dbo.t_stage (txt NVARCHAR(MAX), row_id INT IDENTITY...

Thank you, good find of object_id thingy...

I have added my first connect item now: https://connect.microsoft.com/SQLServer/feedback/details/765777/sys-default-constraints-empty-for-temporary-tables-in-tempdb

BR,

Sergey