Sorting a table in subquery

I recently wanted to sort the records of a table while inserting. We can’t use order by clause in a sub query and  was curious to implement as it solves my issues. Then i found we can use RANK() function for this. Here comes the query,

INSERT INTO TEMP

(EMPID, RK_NBR)

Select EMPID, RANK() OVER (PARTITION BY EMPID ORDER BY ADRS_EFCTV_DT DESC) FROM EMP

We can use this query to when we need to use multiple joins as ORDER BY cannot be used in between. Similarly in some databases like Oracle and Teradata doesn’t allow usage of nested aggregate functions like RANK(COUNT(<COLUMN>)), in such cases we should use QUALIFY function over the result like

Select EMPID, COUNT(*) OVER (PARTITION BY EMPID ORDER BY ADRS_EFCTV_DT DESC) FROM EMP

QUALIFY RANK() OVER (PARTITION BY EMPID  ORDER BY ADRS_EFCTV_DT)=1

Partition, Index in SQL

Partition:

The most widely used partition for improving performance is Range by partition. The data in tables are physically partitioned based on the parameters we choose. It can be daily, weekly, monthly or quarterly based on the volume of data we are processing. Say quarters, the data will be partitioned into four quarters.

CREATE TABLE t1(c1 INT) IN tbsp1, tbsp2, tbsp3

PARTITION BY RANGE (c1)

(STARTING (1) ENDING (99) EVERY (33))

Index:

It is primarily used for better query performance for searching. We can create index for a field or a set of fields. It’s like a primary key index. But it may cause performance degradation. Either we can create clustered index or else unclustered index. Creating clustered index degrades performance as it sorts the data in key order and every time tables will be sorted resulting in longer query execution times.

Best practice:

Create partition and index on same set of columns like below,

……partition by range (Region) create index <idx_name>(Region)

Implementing Nested Aggregation in SQL-Server 2005

In order to implement nested aggregate functions in SQL-Server 2005, we have to use the existing options in a fine tuned manner.

We can’t directly use

Select  max(count(city) , country from dimgeography group by country

Now to implement this use the below query.

Select country,max(count(city))  over() from dimgeography group by country