Menu
is there a simple way to get the number of the current item in a simple SELECT? I need to deliver a column based on a calculation that involves the number of the current index in the select. I am simplifing my problem to an extreme, but roughly speaking, here is an example:
SELECT column1 * ITEMINDEX FROM table
I hope I am being clear. I am using SQL Server. Thank you.
522
To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.
In SQL Management Studio, just type in the query, and hit Control-L (display query execution plan). There, you will be able to see whether any indexes are being used. A "table scan" means the index is not used. An "index scan" means the index is used.
You have the option of displaying your query results on the Run SQL window, as opposed to Data Display windows. To do this, go to View > Data Grid (Ctrl+G). Once you have selected this option, a panel will appear at the bottom of the window - your query results will be displayed there.
Indexing is the way to get an unordered table into an order that will maximize the query's efficiency while searching. When a table is unindexed, the order of the rows will likely not be discernible by the query as optimized in any way, and your query will therefore have to search through the rows linearly.
In SQL Server 2005+:
SELECT m.*, ROW_NUMBER() OVER (ORDER BY column) AS rn
FROM mytable m
SQL does not have concept of implicit row number, that's why you need ORDER BY clause to define the order of rows.
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
