If the object was recently created, you can check the Schema Changes History report, within the SQL Server Management Studio, which “provides a history of all committed DDL statement executions within the Database recorded by the default trace”: You then can search for the create statements of the objects. Among all the information displayed, there … Read more
CASE is an expression that returns a value. It is not for control-of-flow, like IF. And you can’t use IF within a query. Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression must return the same type, … Read more
You can use this code to do it automatically: DECLARE @tableName VARCHAR(MAX) = ‘<MYTABLENAME>’ DECLARE @columnName VARCHAR(MAX) = ‘<MYCOLUMNAME>’ DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) AND PARENT_COLUMN_ID = ( SELECT column_id FROM sys.columns WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName)) IF @ConstraintName IS NOT NULL EXEC(‘ALTER TABLE … Read more
Yes it’s a good practice, see here: When did SQL Server stop putting indexes on Foreign Key columns? scroll down to the Are there any benefits to indexing foreign key columns? section
DATEDIFF(hour, start_date, end_date) will give you the number of hour boundaries crossed between start_date and end_date. If you need the number of fractional hours, you can use DATEDIFF at a higher resolution and divide the result: DATEDIFF(second, start_date, end_date) / 3600.0 The documentation for DATEDIFF is available on MSDN: http://msdn.microsoft.com/en-us/library/ms189794%28SQL.105%29.aspx
CONVERT(VARCHAR(32), HashBytes(‘MD5′, ’[email protected]’), 2)
You can do both in one query using the OVER clause on another COUNT select count(*) RecordsPerGroup, COUNT(*) OVER () AS TotalRecords from temptable group by column_1, column_2, column_3, column_4
I think this is what you’re after: ;WITH x AS ( SELECT value, time, rn = ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Time) FROM dbo.table ) SELECT * FROM x WHERE rn = 1; This may be slow if the resultset is large and there isn’t a good supporting index… EDIT Ah, wait a … Read more
Sample SQL FIDDLE 1) Use CTE to get max ship code value record based on ARDivisionNo, CustomerNo for each Customers WITH cte AS ( SELECT*, row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn] FROM t ) Select * from cte WHERE [rn] = 1 2) To Delete the record use Delete query … Read more
EXCEPT treats NULL values as matching. This query: WITH q (value) AS ( SELECT NULL UNION ALL SELECT 1 ), p (value) AS ( SELECT NULL UNION ALL SELECT 2 ) SELECT * FROM q WHERE value NOT IN ( SELECT value FROM p ) will return an empty rowset. This query: WITH q (value) … Read more