SQL Server: Select Top 0

SQL Server: Select Top 0?

To name columns in a UNION ALL

Be sure to read Alex K.'s answer as well. He has a lot of reasons that I have used as well. This was just the most obvious one.

Cloning a Table without using SELECT TOP (0) * INTO

This should get you there mostly. You may still have to refine it per requirement. Hope this helps

DECLARE @TableName SYSNAME = 'TableName';

DECLARE @SQL VARCHAR(MAX),
@ColumnList VARCHAR(MAX),
@Schema VARCHAR(250);

SELECT @Schema = QUOTENAME(TABLE_SCHEMA)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName;

SET @SQL = 'CREATE TABLE ' + @Schema + '.' + QUOTENAME(@TableName) + '(' + CHAR(13) + CHAR(10);

WITH cte_columns
    AS (
    SELECT
           CHAR(9) + QUOTENAME(COLUMN_NAME) + ' ' + CASE
                                                    WHEN DOMAIN_SCHEMA IS NULL THEN ''
                                                    ELSE QUOTENAME(DOMAIN_SCHEMA)
                                                    END + '.' + CASE
                                                                WHEN DOMAIN_SCHEMA IS NULL THEN DATA_TYPE + QUOTENAME(CHARACTER_MAXIMUM_LENGTH, '(')
                                                                ELSE QUOTENAME(DOMAIN_NAME)
                                                                END + CASE
                                                                      WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL'
                                                                      ELSE ' NULL'
                                                                      END AS ColumnList
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    )
    SELECT
           @ColumnList = COALESCE(@ColumnList + ',' + CHAR(13) + CHAR(10), '') + ColumnList
    FROM cte_columns;

SET @SQL = @SQL + @ColumnList + ')';

PRINT @SQL;

In C#, is SELECT TOP 0 * FROM (/* ... */) s used in conjuction with ADO.NET a good way to determine the column information in a SELECT statement?

You could use GetSchemaTable to do what you want.

There is an example of how to use it here.

SELECT TOP N with UNION and ORDER BY

A Union query works thus: execute the queries, then apply the order by clause. So with

SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
UNION ALL
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T2
ORDER BY [Inactive], [Description]; 

you select five arbitrarily chosen records from #T1 plus five arbitrarily chosen records from #T2 and then you order these. So you need subqueries or with clauses. E.g.:

SELECT * FROM
(
  (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T1
    ORDER BY [Inactive], [Description]
  )
  UNION ALL
  (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T2
    ORDER BY [Inactive], [Description]
  )
) t;

So your workaround is not a workaround at all, but the proper query.

select top 1 * vs select top 1 1

SQL Server detects EXISTS predicate relatively early in the query compilation / optimisation process, and eliminates actual data retrieval for such clauses, replacing them with existence checks. So your assumption:

I now see that the first is 80% of the execution time (relative to the batch of 2) whilst the second is only 20%.

is wrong, because in the preceding comparison you have actually retrieved some data, which doesn't happen if the query is put into the (not) exists predicate.

Most of the time, there is no difference how to test for the existence of rows, except for a single yet important catch. Suppose you say:

if exists (select * from dbo.SomeTable)
...

somewhere in the code module (view, stored procedure, function etc.). Then, later, when someone else will decide to put WITH SCHEMABINDING clause into this code module, SQL Server will not allow it and instead of possibly binding to the current list of columns it will throw an error:

Msg 1054, Level 15, State 7, Procedure BoundView, Line 6

Syntax '*' is not allowed in schema-bound objects.

So, in short:

if exists (select 0 from ...)

is a safest, fastest and one-size-fits-all way for existence checks.

Will Where 0=1 parse full table or just return column names

The SQL Server query optimizer is smart enough to figure out that this WHERE condition can never ever produce a true result on any row, so it doesn't bother actually scanning the table.

If you look at the actual execution plan for such a query, it's easy to see that nothing is being done and the query returns immediately:

select TOP (all)

The largest possible value that can be passed to TOP is 9223372036854775807 so you could just pass that.

Below I use the binary form for max signed bigint as it is easier to remember as long as you know the basic pattern and that bigint is 8 bytes.

declare @t bigint =  case when some_condition then 10 else  0x7fffffffffffffff end;

select top(@t) * 
From table

If you dont have an order by clause the top 10 will just be any 10 and optimisation dependant.

If you do have an order by clause to define the top 10 and an index to support it then the plan for the query above should be fine for either possible value.

If you don't have a supporting index and the plan shows a sort you should consider splitting into two queries.

Diff between Top 1 1 and Select 1 in SQL Select Query

SELECT TOP 1 Means Selecting the very 1st record in the result set

SELECT 1 Means return 1 as the result set

SELECT TOP 1 1 FROM [SomeTable] WHERE <SomeCondition> Means if the condition is true and any rows are returned from the select, only return top 1 row and only return integer 1 for the row (no data just the integer 1 is returned).

---

Related Topics