How to delete the rows having the duplicated values of one or more columns and considered as duplicates? The example in Transact SQL is below.

SET NOCOUNT ON;
CREATE TABLE #t (
  product_name nvarchar(20),
  vendor_name nvarchar(20)
)
GO
INSERT INTO #t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2 …

It's pretty easy on SQL Server but pay attention to the database/server rights of used connection. Usually, the preparing like sp_configure requires a system administrator privileges.

Activate "xp_cmdshell" option (only once)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Create and run wrapping stored procedure …

Clearly, an index build on only one column of the bit type is useless because the selectivity is poor (50% of a table in average). However, when a table has several columns of the bit type, the composite index may be efficient.

For the test example, we'll create a table …

The one of the most frequent asked question in database development is "How to filter the data returned by a query according to some user selected criteria?". To complete the solution with dynamic ordering see my other blog post.

For every method template I will add icons indicating corresponding advantages …

There are several methods to implement the dynamic ordering of result returned by SQL query. The examples of stored procedures below use the Microsoft's demo database AdventureWorks.

USE AdventureWorks
GO

Sequence of IF..ELSE

The sequence of IF..ELSE looks not so pretty but is efficient and rapid. When you …

The rand() Transact SQL function produces a sequence of same numbers in the query. For example:

SELECT rand() AS rand_value, id
FROM
    (      SELECT 1 AS id
     UNION SELECT 2 AS id
     UNION SELECT 3 AS id
    ) AS t1;

This query shows the following result:

rand_value             id
---------------------- -----------
0,581556027773794      1
0 …

How much of disk space is used by tables?

DECLARE @TableInfo TABLE (
    table_name sysname,
    row_count int,
    reserved_size_kb nvarchar(50),
    data_size_kb nvarchar(50),
    index_size_kb nvarchar(50),
    unused_size_kb nvarchar(50)
)

INSERT INTO @TableInfo
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

UPDATE @TableInfo
SET
    data_size_kb     = replace(data_size_kb, 'KB', ''),
    reserved_size_kb = replace(reserved_size_kb, 'KB', ''),
    index_size_kb    = replace(index_size_kb, 'KB', ''),
    unused_size_kb …

Le type "guid" (uniqueidentifier) a été introduit dans la version 7/2000 de SQL Server. Etant utilisé pour les clés primaires ce type avait apporté plusieurs avantages (et inconvénients, bien sur) in implémentation de BDD. En 2002, j'ai fait un essais de performance en comparaison les différents types des données …