I follow a few rules: Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, … Read more
This is a lot simpler than the current proposed solution: IF (OBJECT_ID(‘dbo.FK_ConstraintName’, ‘F’) IS NOT NULL) BEGIN ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName END If you need to drop another type of constraint, these are the applicable codes to pass into the OBJECT_ID() function in the second parameter position: C = CHECK constraint D = … Read more
Cast/Convert the values to a Date type for your group by. GROUP BY CAST(myDateTime AS DATE)
SELECT ( SELECT COUNT(*) FROM tab1 ) AS count1, ( SELECT COUNT(*) FROM tab2 ) AS count2 FROM dual
To avoid duplicate rows for some columns, use user_type_id instead of system_type_id. SELECT c.name ‘Column Name’, t.Name ‘Data type’, c.max_length ‘Max Length’, c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) ‘Primary Key’ FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id … Read more
In Microsoft SQL Server Management Studio you can right-click on the database you wish to backup and click Tasks -> Generate Scripts. This pops open a wizard where you can set the following in order to perform a decent backup of your database, even on a remote server: Select the database you wish to backup … Read more
Wrap the column name in brackets like so, from becomes [from]. select [from] from table; It is also possible to use the following (useful when querying multiple tables): select table.[from] from table;
The general rule is to pick CHAR if all rows will have close to the same length. Pick VARCHAR (or NVARCHAR) when the length varies significantly. CHAR may also be a bit faster because all the rows are of the same length. It varies by DB implementation, but generally, VARCHAR (or NVARCHAR) uses one or … Read more
GROUP BY col1, col2, col3
Set @TableName to the name of your table. declare @TableName sysname=”TableName” declare @Result varchar(max) = ‘public class ‘ + @TableName + ‘ {‘ select @Result = @Result + ‘ public ‘ + ColumnType + NullableSign + ‘ ‘ + ColumnName + ‘ { get; set; } ‘ from ( select replace(col.name, ‘ ‘, ‘_’) ColumnName, … Read more