SELECT TOP SQL alternative for Oracle databases

When querying SQL Server databases you can return the top X number of records that match the query criteria via a SQL statement similar to the following:

[sql]SELECT TOP 100 * FROM tbl_Products WHERE Prod_Desc LIKE ‘*widget*'[/sql]

However, this same SQL statement will not run against an Oracle database because there is no built-in “TOP” function.  To get around this, you can utilize the ROWNUM pseudocolumn.  Example:

[sql]SELECT * FROM tbl_Products WHERE Prod_Desc LIKE ‘%widget%’ AND ROWNUM < 101[/sql]

Query an Oracle Linked Server table from SQL Server

I have an Oracle Linked Table setup in my SQL Server database.  In order to query it’s internal tables (without needing to use OpenQuery), I use the following syntax:

[SQL]

SELECT * FROM [LinkedServerName]..[Schema].[TableName]

[/SQL]

Because the query is executed within SQL Server, you can use SQL Server-specific functions such as TOP, GetDate(), etc.  In order to pass a query directly to the Oracle table, using Oracle-specific functions/variables (TO_CHAR, SYSDATE, etc), and return the related dataset, use OpenQuery.

Create an AutoNumber field in SQL Server

• Create a new column of numeric type
• Uncheck Allow Nulls
• Set Identify Specification > Is Identity to Yes
• Make sure Identity Increment and Identity Seed are set to 1
• Right-click the column and select Set Primary Key