This may be a pretty naive and stupid question, but I’m going to ask it anyway I have a table with several fields, none of which are unique, and a primary key, which obviously is. This table is accessed via the non-unique fields regularly, but no user SP or process access data via the primary key. Is th…
Tag: sql-server
Only inserting a row if it’s not already there
I had always used something similar to the following to achieve it: …but once under load, a primary key violation occurred. This is the only statement which inserts into this table at all. So does this mean that the above statement is not atomic? The problem is that this is almost impossible to recreate…
Change a Nullable column to NOT NULL with Default Value
I came across an old table today with a datetime column called ‘Created’ which allows nulls. Now, I’d want to change this so that it is NOT NULL, and also include a constraint to add in a default …
select from access database file and insert to sql Database
I have an access database file (test.mdb) and I need to write a stored procedure which will select some records from tblTest in test.mdb and insert them into tbsqlTest in my sql database . ==> I need a SP like this : Answer If you’re willing to permit Ad Hoc Distributed Queries on your SQL Server, yo…
Is a Union Query needed
I need a little help writing a query. I have this data… I need to query the data and have an output something like this The idea here is to select the direction, the LowNum and then the nextNumber. Also when the direction changes to start over. (That’s why I think I need a Union, maybe get the Nor…
TSQL – make a literal float value
I understand the host of issues in comparing floats, and lament their use in this case – but I’m not the table author and have only a small hurdle to climb… Someone has decided to use floats as you’…
Is there a combination of “LIKE” and “IN” in SQL?
In SQL I (sadly) often have to use “LIKE” conditions due to databases that violate nearly every rule of normalization. I can’t change that right now. But that’s irrelevant to the question. Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readabilit…
Clustered index – multi-part vs single-part index and effects of inserts/deletes
This question is about what happens with the reorganizing of data in a clustered index when an insert is done. I assume that it should be more expensive to do inserts on a table which has a clustered …
Simple check for SELECT query empty result
Can anyone point out how to check if a select query returns non empty result set? For example I have next query: Should I do something like next: to test if result set is not empty? Answer Use @@ROWCOUNT: According to SQL Server Books Online: Returns the number of rows affected by the last statement. If the n…
What is Service Broker in SQL Server?
What is Service Broker in SQL Server and is it meaningful to enable it in a simple database, not in a distributed database? Answer SQL Service Broker is an extension mechanism that allows you to queue events for asynchronous processing. There is no intrinsic harm in enabling the broker. If it’s not used…