Limiting SQL Query Result Set to few number

Hi All,

With TABLESAMPLE keyword we can restrict the Result of the SQL Query, where rows will be selected randomly from the table

For example one table have 10000 Rows, then with following query we will get approximate 10 % of the rows from table

SELECT * FROM [TABLE_NAME] TABLESAMPLE (10 PERCENT)

If the requirement is for the same set of records every time then we have to use REPEATABLE keyword at end with some seed value as shown below

SELECT * FROM [TABLE_NAME] TABLESAMPLE (10 PERCENT) REPEATABLE (205)

Find empty tables in SQL Database

Hi friends,

Here is the SQL code to find the Table Names and their owner of the Database where 0 records are available.

;With CTE(TableName, SchemaName, RowCnt)
As
(
 Select
T.Name As TableName, S.name As SchemaName, R.RowCnt
 from Sys.tables As T Inner Join
 (
Select
 ID, rowcnt As RowCnt
From SysIndexes Where IndId < 2
 ) As R On T.[Object_Id] = R.Id
 Inner Join sys.schemas As S On S.[schema_id] = T.[schema_id]
)

Select
 TableName, SchemaName
From CTE Where RowCnt = 0 

Convert Money to Varchar using SQL

Hi,

When we try to convert Money variable to Varchar in SQL it automatically round off the value to 2 decimal places.

Following is example

DECLARE @UnitCost MONEY

SET @UnitCost = .0167

SELECT
   @UnitCost,
   CAST(@UnitCost AS VARCHAR(30)),
   CAST(@UnitCost AS DECIMAL(30, 4)),
   CAST(CAST(@UnitCost AS DECIMAL(30, 4)) AS VARCHAR(30))


but if you want value to be rounded off upto 4 decimal places then please see the following code

DECLARE @UnitCost MONEY

SET @UnitCost = .016789

SELECT
    @UnitCost, convert(varchar(10),@unitcost,2),