You are looking for an indication if the table is empty. For that SQL has the EXISTS keyword. If you are doing this inside a stored procedure use this pattern: IF(NOT EXISTS(SELECT 1 FROM dbo.MyTable)) BEGIN RAISERROR(‘MyError’,16,10); END; IF you get the indicator back to act accordingly inside the app, use this pattern: SELECT CASE … Read more
Well, I believe it’s an “Old but Gold” question. The answer is: “It depends!”. The performances are such a delicate subject that it would be too much silly to say: “Never use subqueries, always join”. In the following links, you’ll find some basic best practices that I have found to be very helpful: Optimizing Subqueries … Read more
A quick-and-dirty way of fixing these is to select the rows into a new physical table like so: SELECT * INTO dbo.MyNewTable FROM <the rest of the offending query goes here> …and then compare the schema of this table to the schema of the table into which the INSERT was previously going – and look … Read more
@tableName Table variables are alive for duration of the script running only i.e. they are only session level objects. To test this, open two query editor windows under sql server management studio, and create table variables with same name but different structures. You will get an idea. The @tableName object is thus temporary and used … Read more
I have managed to resolve the issue, I solved it by following this link’s suggestion: http://www.mattbutton.com/2011/06/01/sql-profiler-templates-missing/ The following comes from the link: If you are connecting to a SQL server with the SQL profiler and none of your templates are showing up, compare the versions of the SQL profiler you are running and the version … Read more
You can use the output clause to capture the modified/inserted rows to a table variable and use that with a delete statement after the merge. DECLARE @T TABLE(EmployeeID INT); MERGE Target1 AS T USING Source1 AS S ON (T.EmployeeID = S.EmployeeID) WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE ‘S%’ THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName) … Read more
It enables you to use the $ROWGUID variable instead of the column name. It’s used mostly for merge replication. It doesn’t force the GUID column for PK, you can only declare PKs explicitly. The answer to your last question is no, unless you plan on implementing Merge Replication.
Heap storage has nothing to do with these heaps. Heap just means records themselves are not ordered (i. e. not linked to one another). When you insert a record, it just gets inserted into the free space the database finds. Updating a row in a heap based table does not affect other records (though it … Read more
Assuming you have created a user in this database associated with the AD login, e.g. CREATE LOGIN [domain\user] FROM WINDOWS; GO USE your_database; GO CREATE USER [domain\user] FROM LOGIN [domain\user]; GO Then you merely have to follow the same syntax. Because \ is not a standard character for an identifier, you need to escape the … Read more
columns have name and type attributes for c in columns: print c.name, c.type