Identity and Sequence

Identity Sequence
Table Specific Table Independent
You cannot obtain the new value in your application before using it You can obtain the new value before using it in an INSERT statement
You cannot add or remove the property from an existing column You can add or remove a default constraint defined for a column with an expression that generates a new sequence value (extension to the standard)
You cannot generate new values in an UPDATE statement when needed, rather only in INSERT statements You can generate new values in an UPDATE statement
The semantics of defining ordering in a multi-row insert are confusing, and in SELECT INTO statements are actually not guaranteed The semantics of defining ordering in a multi-row insert are very clear using an OVER clause (extension to the standard), and are even allowed in SELECT INTO statements
You cannot define: minimum and maximum values, whether to allow cycling, and caching options You can define minimum and maximum values, whether to allow cycling, and a cache size option for performance (extension to the standard)
You can reseed an identity property, but you cannot change the step size You can alter any of the properties of a sequence object besides the data type, including the current value, increment, minimum value, maximum value, cycle and cache size
You cannot obtain a whole range of new identity values in one shot, letting the application assign the individual values You can obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range (extension to the standard), letting the application assign the individual values for increased performance

www.sqlserver-traning.com
Advertisements

SQL Optimisation Tips

50 Database Optimization TIPS

Database Optimization TIPS helps any system to perform work efficiently and quickly.

SQL Server Performance tuning is very wide subject and there are a lot of different pieces to troubleshoot a poor performing application.

  1. Connectivity / Network
  2. Server Hardware
  3. Operating System
  4. SQL Server
  5. Database
  6. Query

50 Database Optimization TIPS

The basic principle of Optimization / Tuning is

Profile First and then Optimize

The basic rule of optimization is to never assume or estimate – always verify, with actual data stats. The process of collecting performance metrics and determining performance issues is called profiling.

Tools for Profiling – Data Capture

There are n number of tools which come in market to capture performance data but I am going to list tool, which comes free from Microsoft

  • Performance Monitor – By using Windows Performance Monitor (PERFMON), we can analyze how SQL Server database is performing, both in real time and by collecting log data for later analysis. To run Windows PERFMON, on the Start menu, point to RUN and type ‘PERFMON’ and hit enter.
  • SQL Server Profiler / Trace – Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of SQL Server. Trace runs in the background. To run SQL Server Profiler, on the Start menu, point to All Programs, Microsoft SQL Server, Performance Tools, and then click SQL Server Profiler.
  • SQL Server DMV – Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Quick List of all SQL Server DMV

TIPS for Database Optimization

Database Performance Booster TIPS

TIPS for Database Maintenance

  • Schedule Index Maintenance, Rebuild / Reorganize indexes to keep fragmentation level low
  • Update Statistics
  • Schedule DBCC CHECKDB (Database Consistency Check) to checks logical and physical integrity of all database objects
  • Regular Database Backups, this is not an database optimization tip but an mandatory data safeguard.
  • Shrink Database, keep the Database size small if possible, release the space back to OS.

TIPS for Physical Database Layout (Database File Placement)

  • Locate logs and the tempdb database on separate devices from the data.
  • Provide separate devices for heavily accessed tables and indexes.
  • Use the correct RAID configuration, RAID 10 is best, if you can afford
  • Initialize databases data file and logs file to avoid automatic growth and fragmentation and performance impact.

TIPS for Database Schema Optimization (Database Design)

  • Separate online analytical processing (OLAP) and online transaction processing (OLTP) systems, wherever possible.
  • Normalize first, denormalize later for performance.
  • Define all primary keys and foreign key relationships.
  • Define all unique constraints and check constraints.
  • Choose the most appropriate data type.
  • Use indexed views for denormalization.
  • USE Partitioning to Partition tables vertically and horizontally.

TIPS for Index Optimizations

  • Manage index fragmentation, keep is low.
  • Create indexes based on usage, use DMVs
  • Keep clustered index keys as small as possible. Watch Kimberly MCM Video for more details
  • Consider range data for clustered indexes.
  • Create an index on all foreign keys.
  • Create highly selective indexes.
  • Create a covering index for often-used, high-impact queries.
  • Use multiple narrow indexes rather than a few wide indexes.
  • Create composite indexes with the most restrictive column first.
  • Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
  • Remove unused indexes.
  • Use the Index Tuning Wizard as assistance

TIPS for QUERY Optimizations (SQL Statements)

  • Fully qualify database objects, use schema name with object name
  • Use locking and isolation level hints to minimize locking. specify NOLOCK hint when running queries
  • Return only the rows and columns needed best way to reduce IO
  • Avoid expensive operators such as NOT LIKE.
  • Avoid explicit or implicit functions in WHERE clauses.
  • Know the performance and scalability characteristics of queries.
  • Write correctly formed queries.
  • Use stored procedures or parameterized queries.
  • Use the sp_executesql instead of the EXECUTE statement for dynamic SQL statements
  • Minimize cursor use.
  • Avoid long actions in triggers.
  • Use temporary tables and table variables appropriately.
  • Limit query and index hint use.
  • Use IF EXISTS (SELECT 1) instead of (SELECT *)

TIPS for Transactions (DML Statements)

  • Avoid long-running transactions.
  • Avoid transactions that require user input to commit.
  • Access heavily used data at the end of the transaction.
  • Try to access resources in the same order.
  • Use isolation level hints to minimize locking.
  • Ensure that explicit transactions commit or roll back.

Tips for Stored Procedures

  • Use Set NOCOUNT ON in stored procedures.
  • Do not use the sp_prefix for custom stored procedures.

TIPS for XML

  • Avoid OPENXML over large XML documents.
  • Avoid large numbers of concurrent OPENXML statements over XML documents.