latest / Min /Max Value Group wise

select f.Kcode, f.RunningBalance
from (
  select Kcode,max(createddatetime) as latestdate
from tblAgentTran
group by Kcode
) as x inner join tblAgentTran as f on f.Kcode = x.Kcode and f.createddatetime = x.latestdate;

Hello All,

     From Recent Search , Vinay find good  & quick solution for inserting record from one old table to new table, with specific columns

 

 INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where [condition]
using this query we can insert some records form old table to new table with given condition.
thanks vinay for your solution.

Hi All,

   Recently @ time of checking records in Database table with case sensitive.

  We are firing query like

        select * from Tablename where strPassword = 'Vinay343@'

  at the same time if we fire query like

        select * from Tablename where strPassword = 'VINAY343@'

 we are getting same result so technically me & vinay think that it is not good to get result if user send case sensitive input

 after searching we found the solution that if we want to check exact match use query like this

  select * from Tablename where strPassword COLLATE Latin1_General_CS_AS='Vinayshah0!'

here We get exact match as per the input

Thanks Vinay for sharing this information

Cheers!!!!

Calculate Age from Sql function

In one of my interview , interviewer asked me how should  you calculate age from date of birth.

     As i m not too strong in SQL server , i have started from .Net code  for calculating Age from  DOB.
     but not success ed in that.  after searching in books & discussing with friends[Vinay Shah]
     come to conclusion that we can get Age  from Sql Function DATEDIFF.

   in SQL Server 2005 u can use Datediff function

   Sample Query = select datediff (year, '1988-05-17 00:00:00.000' , getDate())

  from above query you will get the Age in year  OUTPUT = 24
 instead of  1988-05-17 00:00:00.000   you can enter your DOB & get your Age.


  in Oracle 

   select ROUND((SysDate - DOB)/365) as Age from cust_table

  here you we will get age of the customers where DOB should have Date DataType

      Using Datediff function we can calculate not only age in years but   also following

              

Unit of time	Query	Result
NANOSECOND	SELECT DATEDIFF(NANOSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133')	4321300
MICROSECOND	SELECT DATEDIFF(MICROSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133')	4321
MILLISECOND	SELECT DATEDIFF(MILLISECOND,'2011-09-23 17:15:22.004','2011-09-23 17:15:22.548')	544
SECOND	SELECT DATEDIFF(SECOND,'2011-09-23 17:15:30','2011-09-23 17:16:23')	53
MINUTE	SELECT DATEDIFF(MINUTE,'2011-09-23 18:03:23','2011-09-23 17:15:30')	-48
HOUR	SELECT DATEDIFF(HH,'2011-09-23 18:03:23','2011-09-23 20:15:30')	2
WEEK	SELECT DATEDIFF(WK,'09/23/2011 15:00:00','12/11/2011 14:00:00')	12
DAY	SELECT DATEDIFF(DD,'09/23/2011 15:00:00','08/02/2011 14:00:00')	-52
DAYOFYEAR	SELECT DATEDIFF(DY,'01/01/2011 15:00:00','08/02/2011 14:00:00')	213
MONTH	SELECT DATEDIFF(MM,'11/02/2011 15:00:00','01/01/2011 14:00:00')	-10
QUARTER	SELECT DATEDIFF(QQ,'01/02/2011 15:00:00','08/01/2011 14:00:00')	2
YEAR	SELECT DATEDIFF(YY,'01/02/2011 15:00:00','01/01/2016 14:00:00')	5

If we want to Calculate Sum of 2 tables count we can use following  query

SELECT SUM(c)
FROM (
  SELECT COUNT(id) AS c FROM table1
  UNION ALL
  SELECT COUNT(id) FROM table2
  ) as b

Delete Duplicate Rows

/* Create Table with 7 entries - 3 are duplicate entries */

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

INDEXING IN SQL SERVER

Using the Right Indexes for Optimal Performance Query optimization is a complex game with its own rules.

Let’s look at three examples to discover when SQL Server Query Optimizer uses clustered indexes and non-clustered indexes to retrieve data and when to use the primary key (PK) to influence performance.

Example 1: Default Index Usage

Let’s look first at Query Optimizer’s default use of indexes.

Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (relative to the batch).

In this example, no query hint is specified, so Query Optimizer can use any index it wants to use, which results in optimal performance.

Notice that even though ContactID (the primary key [PK] of the Contact table) is retrieved in Query 1, Query Optimizer does not use a primary key clustered index; instead, it uses a non-clustered index. In Query 2, on the other hand, Query Optimizer uses a clustered index on PK where all columns (*) are retrieved.

This may be surprising. It is a common belief that when PK columns are used and no other condition or joins are used, Query Optimizer will use a PK clustered index to return the results of a SELECT statement. However, this is not always true.

Example 2 : Forcing a Primary Key (PK) Clustered Index

Let’s see how performance changes when a primary key clustered index is used to retrieve data.

Query 1’s query cost (relative to the batch) is equals to Query 2’s query cost (also relative to the batch).

In this example, we are using a primary key clustered index to retrieve data. The same execution plan is created whether we retrieve only one column or all the columns.

Example 3: Forcing Non-Clustered Index

Now consider two queries in which a non-clustered index is used to retrieve data.

Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (also relative to the batch).

In this example, we are using a non-clustered index to retrieve data. From the execution plan, it is very clear that retrieving only one column is much faster than retrieving all the columns from the Contact table. When all the columns from a table are selected, a PK clustered index is clearly the best option.