Workload type Before creating an index, you should understand the workload type of the database. On Online Transaction Processing (OLTP) database, workloads are used for transactional systems, in which most of the submitted queries are data modification queries. In contrast, Online Analytical Processing (OLAP) database workloads are used for data warehousing systems, in which most of... » read more
If you want SQL Server to automatically provide values for the new column, make it an identity. Note: this will add a new column at the end of the table. You will need to recreate the table if you want the new PK column to be the first column. There is a way around this... » read more
The following example creates a table and defines a primary key on the column TransactionID in the AdventureWorks database. PAD_INDEX PAD_INDEX = { ON | OFF } Specifies index padding. The default is OFF. ON: The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index. OFF or... » read more
Sources: https://dataedo.com/kb/query/sql-server/list-all-table-constraints
All tables with Primary Key All tables missing Primary Key t.type – object type: Table View c.type – type of constraint: Primary key Unique key Foreign key Check constraint Default constraint
sp_help table01 Table Table Columns Identity Row Guid Column Filegroup Index Constraint Table is Referenced By Foreign Key
Use TRUNCATE instead of DELETE If you need to clear the data in a table, use TRUNCATE instead of DELETE. TRUNCATE lock the table instead of at each row. No triggers are activated and no logs are generated resulting in faster performance. Note: TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE... » read more
The following SQL Server Not Exists query will find the Employees whose Occupation is neither Skilled Manual nor Clerical. Note: Using NOT EXISTS may have a performance hit. Try to use LEFT JOIN and WHERE column is NULL to test if there is a performance improvement. Sometimes, NOT EXISTS may be faster, and sometimes it... » read more
SSMS -> Database Name -> Task -> Generate Script Select object -> Next Click on “Advanced” in the “Types of Data to Script” select “Data only” Generated Script
All SP Exec All SP one SP at a time Following script will try to add permissions (execute permissions) on all procedures on all databases on the server for the role defined.