| ||||||
MSSQL TipsTips and tricks for MSSQL and T-SQL useful resources and code snippets. Indexes, advanced queries, new features, useful functions and algorithms. How to deal with system tables and views. Transaction log, system procedures, security and many other MSSQL related resources.MSSQL 2005: Calculate Indexes Size via dm_db_index_physical_statsSometimes we want to calculate how much space do table indexes get. We can use sp_spaceused system stored procedure to measure how much does specific object take physical space. In MSSQL 2005 we can also use system procedure sys.dm_db_index_physical_stats to list how many indexes are present on a table, their type and how big they are [ ... ] posted @ Tuesday, November 1, 2005 9:56 PM | Your thoughts T-SQL Query: Select Middle RecordSELECT TOP 1 query in T-SQL helps to find the first or the last record of the table data sorted by some criteria. But what if we need to find exactly middle record entry in the table ? Below is a small T-SQL query snippet that demonstrates a technique how to get middle record in a single query [ ... ] posted @ Saturday, July 9, 2005 1:50 AM | Your thoughts Yukon: New Features in T-SQLThis article was actually published 2 years before MSSQL 2005 final release. It describes new features and syntax in T-SQL language in the future version of MSSQL. Top, pivot, cross apply, CTE, xquery and XML, etc. The article was written in russian [ ... ] posted @ Friday, January 21, 2005 11:55 PM | Your thoughts T-SQL: Leap Year CheckT-SQL function that checks for leap year using date shifting technique. Just add one day to 28th of February that year and check for the day of month [ ... ] posted @ Sunday, April 11, 2004 8:57 PM | Your thoughts HowTo Convert Table Values to Comma-Delimited StringThe function below demonstrates a technique how to convert table values into comma-delimited string with one query. It may be useful when need to get data from 2 tables related as one-to-many [ ... ] posted @ Friday, April 2, 2004 4:36 AM | Your thoughts T-SQL: Generate Sequences in a Loop QueryArticle that demonstrates some technique of how to generate sequences with SQL. We can boost performance by using a single query, that simulates a sequence - Loop Query. Also it is useful to create tables on the fly when you have to deal with iterating, sequencing data such as calendar dates, sequence numbers [ ... ] posted @ Wednesday, March 24, 2004 3:52 AM | Your thoughts T-SQL: Optional Parameters in Select Query (Using ISNULL)SQL tip how to deal with optional parameters in select, procedure. For example you have some application with advanced search UI where user does not have to fill all search criteria. We can make it with one small trick - using ISNULL function [ ... ] posted @ Saturday, February 28, 2004 5:09 AM | Your thoughts MSSQL Tip: Get Primary Table in RelationshipQuery in MSSQL that demonstrates how to get Primary table name, having the name of Foreign table and column in a relation. Example makes use of the following MSSQL system tables: sysobjects, syscolumns and sysforeignkeys [ ... ] posted @ Saturday, February 21, 2004 1:53 AM | Your thoughts MSSQL Tip: Get Table Primary Key Column(s)We know table name and need to get its Primary Key column(s) programmatically. In order to do it we will use the following system tables: sysobjects - for all user objects, sysindexkeys - for indexes and keys, syscolumns - for tables columns. The query [ ... ] posted @ Sunday, February 1, 2004 2:41 AM | Your thoughts ROWNUM [ PL/SQL ] Analog for T-SQLThere is no ROWNUM Analog in T-SQL. ROWNUM is used in PL/SQL to to get the number of rows the query returns. There is no easy way to do the same trick in T-SQL query. Small snippet below demonstrates the easy way to achieve the same goal using inner view. [ ... ] posted @ Monday, January 19, 2004 7:39 AM | Your thoughts MSSQL Tip: Get Connected Columns in Related TablesQuery in MSSQL that demonstrates how to get Foreign Key related columns, having the names of Primary and Foreign tables. Example makes use of the following MSSQL system tables: sysobjects, syscolumns and sysforeignkeys [ ... ] posted @ Monday, January 19, 2004 7:35 AM | Your thoughts How To Shrink Transaction Log in MSSQL 2000... our MSSQL 2000 database transaction log has grown very big so we need to cut it manually during maintenance in order to save some disk space. Here are the steps to truncate database transaction log [ ... ] posted @ Sunday, January 18, 2004 3:43 AM | Your thoughts T-SQL: Output Parameter in sp_executesqlsp_executesql is MSSQL system procedure used to execute dynamic SQL (SQL string built dynamically). Sometimes there is a need to return output parameter from that dynamic SQL. Below is a small T-SQL code snippet that demonstrates how to do it [ ... ] posted @ Tuesday, December 23, 2003 7:44 PM | Your thoughts MSSQL: Change tables owner to dbo with sp_changeobjectownerAll tables owned by dbo schema is usually best practices in the database application development. If its not the case - the following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. [ ... ] posted @ Friday, December 5, 2003 5:40 PM | Your thoughts MSSQL: Recompile Stored Procedures, Views, FunctionsSmall MSSQL code snipped written solely for maintenance purposes. Sometimes after massive MSSQL schema update to online scalable production environment there is a need to recompile all stored procedures, user-defined functions and views in the database in order to MSSQL will refresh stores procedures execution plans stored in memory in order to reflect recent schema changes. [ ... ] posted @ Sunday, November 30, 2003 1:54 PM | Your thoughts MSSQL Tip: Rebuild Clustered IndexesSQL code that rebuilds all clustered indexes in the database. Uses cursor to run through all tables. Uses dbcc dbreindex command. Don't use on large tables on production databases since each rebuild hangs table up. [ ... ] posted @ Sunday, November 30, 2003 1:52 PM | Your thoughts T-SQL: Aggregate Values Multiplication Like SUM Aggregates Values SumSmall T-SQL trick that shows how to aggregate multiplication of column values same way as SUM function aggregates the sum of column values. [ ... ] posted @ Sunday, November 30, 2003 1:36 AM | Your thoughts SQL Tip: HowTo Convert Delimited String to TableT-SQL small code-snippet that shows how to convert comma-delimited string to table. The snippet is formatted as user-defined function. [ ... ] posted @ Sunday, November 30, 2003 1:31 AM | Your thoughts
|
|
|||||
| ||||||
