I won’t address the READ UNCOMMITTED argument, just your original question. Yes, you need WITH(NOLOCK) on each table of the join. No, your queries are not the same. Try this exercise. Begin a transaction and insert a row into table1 and table2. Don’t commit or rollback the transaction yet. At this point your first query … Read more
In one statement: No. In one transaction: Yes BEGIN TRANSACTION DECLARE @DataID int; INSERT INTO DataTable (Column1 …) VALUES (….); SELECT @DataID = scope_identity(); INSERT INTO LinkTable VALUES (@ObjectID, @DataID); COMMIT The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client … Read more
To see all tables in another schema, you need to have one or more of the following system privileges: SELECT ANY DICTIONARY (SELECT | INSERT | UPDATE | DELETE) ANY TABLE or the big-hammer, the DBA role. With any of those, you can select: SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = ‘TABLE’ AND … Read more
It depends on a lot of factors – but most crucially: complexity of calculations (prefer doing complex crunching on an app-server, since that scales out; rather than a db server, which scales up) volume of data (if you need to access/aggregate a lot of data, doing it at the db server will save bandwidth, and … Read more
You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN, MAX or SUM). A simple example to understand why this happens: Imagine you have a database like this: FOO BAR 0 A 0 B and you run SELECT … Read more
Use something like this – SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER(‘%PriceOrder%’) or SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER(‘%PriceOrder%’)
A 1:1 relationship typically indicates that you have partitioned a larger entity for some reason. Often it is because of performance reasons in the physical schema, but it can happen in the logic side as well if a large chunk of the data is expected to be “unknown” at the same time (in which case … Read more
Here it is: SELECT OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME (f.referenced_object_id) = ‘YourTableName’ This way, you’ll get the referencing table and column name. Edited to use sys.tables instead of generic sys.objects as per comment … Read more
In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full). In general; A CTE can be used recursively; a sub-query … Read more
If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it. From Books Online: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the … Read more