 |
Extract records by their record number
|
| Total Hit (2926) |
SQL Server, and the SQL language in general, doesn't support record numbers, so you can't extract a set of records if you know their position in the resultset. This missing capability would be extremely convenient when displaying pages of data in an ASP program. For example, if each page contains 10
....Read More |
Rating
 |
|
|
|
Avoid error 1540 in Join queries
|
| Total Hit (2775) |
When you plan your queries, you should avoid the use of wildcard keyword * - which means "retrieve any field" - mainly because it may decrease performances, since the engine allocates space for the entire row; use explicit field declaration instead, such as:
SELECT field1, field2 FROM table1
....Read More |
| Rating
|
|
|
|
Create temporary or regular table with SELECT INTO
|
| Total Hit (3156) |
The SELECT INTO statement is a combination of the SELECT and INSERT T-SQL commands, that lets you create a new table from a subset of the rows and/or the columns of another table. The target table of this command is often a temporary table:
«Code LangId=6»
SELECT au_fname, au_lname INTO #authors
....Read More |
| Rating
|
|
|
|
GROUP BY and UNION may return unsorted resultsets
|
| Total Hit (3159) |
In SQL Server 6.5 and previous versions, the GROUP BY clause in a SELECT statement was carried out by temporarily sorting the resultset before grouping similar rows, therefore the end result was always sorted. For this reasons, many programmers omitted the ORDER BY clause, because the result was sor
....Read More |
| Rating
|
|
|
|
Null values in WHERE clauses
|
| Total Hit (3157) |
A SELECT query returns all the rows for which the WHERE clause returns True. However, many developer - especially those accustomed to other programming languages, such as VB - get confused on this point, and assume that the query returns the rows for which the WHERE clause returns any non-False valu
....Read More |
| Rating
|
|
|
|
Quickly copy records between tables with same structure
|
| Total Hit (2946) |
The SQL language offers a simple and effective way to quickly move all records from a table to another table with same field structure, that is same fields' name, type and position:
«Code LangId=6»
INSERT Customers SELECT * FROM New_Customers
-- you can optionally complete the move by
-- delet
....Read More |
| Rating
|
|
|
|
Statistic functions that take Null values into account
|
| Total Hit (2834) |
Statistic functions - that is MAX, MIN, SUM, AVG, VAR, VARP, STDEV and STDEVP - don't take Null values into account. Usually this isn't a problem with the SUM function, but it can be an issue with the others. For example, you can evaluate the average value of a group of records keeping Null values i
....Read More |
| Rating
|
|
|
|
The ALL clause can speed up UNION statements
|
| Total Hit (2824) |
If you omit the ALL clause in an UNION statement, SQL Server must delete duplicate values, which in turn means that it has to sort the two sub-resultsets that have to be combined. Needless to say, this is a time-consuming operation.
In most cases, you decide whether to use the ALL clause dependin
....Read More |
| Rating
|
|
|
|
The fastest way to delete all the rows in a table
|
| Total Hit (2812) |
The standard way to delete all the rows in a SQL Server table is a DELETE statement without a WHERE clause:
«Code LangId=6»
DELETE FROM MyTable
«/Code»
However, in most cases you can speed up your code remarkably with the TRUNCATE TABLE command: «Code LangId=6»
TRUNCATE TABLE MyTable «/Code
....Read More |
| Rating
|
|
|
|
Understanding the TOP WITH TIES clause in SELECT queries
|
| Total Hit (3031) |
The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group. To see what this means in practice, execute the following query against the Pubs database in SQL Server 7.0:
«Code LangId=6»
SELECT TOP 5 price, titl
....Read More |
| Rating
|
|
|
|
Update SQL Server database with Updategrams
|
| Total Hit (2632) |
You can use XML to transfer both data and commands. In this column I'll show how you can update a database by means of XML commands sent over the HTTP protocol and, even more interesting, how to perform bulk uploads with these commands.
An updategram is a piece of XML data that contains informati
....Read More |
| Rating
|
|
|
|
|
|
Displaying random data from SQL table
|
| Total Hit (2788) |
Following query will display any 5 random rows from Products table of Northwind database
NewId() function genereates New GUID.......Try this code it works fine
«Code LangId=6»
select top 5 * from products order by NewId() «/Code»
|
| Rating
|
|
|
|
Working with COM objects from within T-SQL
|
| Total Hit (4621) |
This article includes
- Introduction
- General concepts
- OLE Automation Stored Procedures
«OL»«LI»sp_OACreate
«LI»sp_OADestroy
«LI»sp_OAGetProperty
«LI»sp_OASetProperty
«LI»sp_OAMethod
«LI»sp_OAGetErrorInfo
«LI»sp_OAStop«/OL»
- Example: generate script
- Literature
------
....Read More |
| Rating
|
|
|
|
OLE Automation in SQL server
|
| Total Hit (3560) |
This is an example of a Transact-SQL statement batch that uses the OLE Automation stored procedures to create and use an SQL-DMO SQLServer object. Portions of the code are used as examples in the stored procedure references.
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DE
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|