What you’re looking for is indeed part of the ANSI standards SQL:92, SQL:1999 and SQL:2003, ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not… In SQL Server 2008, you can define a … Read more
UPDATE im SET mf_item_number = gm.SKU –etc FROM item_master im JOIN group_master gm ON im.sku = gm.sku JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID WHERE im.mf_item_number like ‘STA%’ AND gm.manufacturerID = 34 To make it clear… The UPDATE clause can refer to an table alias specified in the FROM clause. So im in this case … Read more
Is it correct to do the following? IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist). Instead, for a permanent table you can use IF OBJECT_ID(‘dbo.Scores’, ‘U’) IS NOT NULL DROP TABLE dbo.Scores; … Read more
The MSDN documentation for datetime recommends using datetime2. Here is their recommendation: Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications. datetime2 has larger … Read more
Most databases support != (popular programming languages) and <> (ANSI). Databases that support both != and <>: MySQL 5.1: != and <> PostgreSQL 8.3: != and <> SQLite: != and <> Oracle 10g: != and <> Microsoft SQL Server 2000/2005/2008/2012/2016: != and <> IBM Informix Dynamic Server 10: != and <> InterBase/Firebird: != and <> … Read more
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID ) In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY: SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE … Read more
Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table: EXEC sp_fkeys ‘TableName’ You can also specify the schema: EXEC sp_fkeys @pktable_name=”TableName”, @pktable_owner=”dbo” Without specifying the schema, the docs state the following: If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply. … Read more
If you want to disable all constraints in the database just run this code: — disable all constraints EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all” To switch them back on, run: (the print is optional of course and it is just listing the tables) — enable all constraints exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE … Read more
You don’t quite have SQL Server’s proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this: UPDATE t1 SET t1.CalculatedColumn = t2.[Calculated Column] FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t1.CommonField = t2.[Common Field] WHERE t1.BatchNo = ‘110’; … Read more
You can obtain this information and much, much more by querying the Information Schema views. This sample query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’Customers’ Can be made over all these DB objects: CHECK_CONSTRAINTS COLUMN_DOMAIN_USAGE COLUMN_PRIVILEGES COLUMNS CONSTRAINT_COLUMN_USAGE CONSTRAINT_TABLE_USAGE DOMAIN_CONSTRAINTS DOMAINS KEY_COLUMN_USAGE PARAMETERS REFERENTIAL_CONSTRAINTS ROUTINES ROUTINE_COLUMNS SCHEMATA TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLES VIEW_COLUMN_USAGE VIEW_TABLE_USAGE VIEWS