Entries Tagged as 'Sql Server'
Sql Server Rank() v/s Dense_rank()
Posted by Bharat Patel on September 28, 2011 |
|
Hello Friends,
Some days before I was googling on sql server's system function and I got one site having sql server query puzzle. The site have very interesting puzzle. I got a puzzle during surfing and it is to find out second highest salary of each department and tie salary should also count. There are inbuilt functions in sql server to give rank as per result set.
The Rank() function of sql server returns the position of value within a partition of a result set. Rank() function left gaps where ranks are tie.
The Dense_rank() function of sql server returns the position of value within a partition of a result set. Dense_rank() function does not left gaps where ranks are tie.
Syntex :
Rank() over([partition_by] <order_by>)
Dense_rank() over([partition_by] <order_by>)
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)
select *,RANK() over(partition by department order by salary desc) as ranking
from @Employees
select *,DENSE_RANK() over(partition by department order by salary desc) as ranking
from @Employees
-- Final query
select * from (
select *,RANK() over(partition by department order by salary desc) as ranking
from @Employees
) As T where T.ranking = 2
Output
sql server(store procedure) mechanism convert empty string date parameter to initial date(1900-01-01)
Posted by Bharat Patel on August 27, 2011 |
|
Hello Friends,
It may you know about it but I would like to share this thing with you. We were working on one of project where we faced issue. we are storing data through sql server store procedure using date parameter. As per our logic, the date parameter has not date value it may be empty string. Whenever date parameter has empty string sql server store initial date(1900-01-01) in the date field. We checked our whole logic to find out how the initial date come if date parameter has an empty string. After debugging we found out it due to sql server mechanisum for convert empty string date value to initail date(1900-01-01). So make sure what exactly you want to store in table either selected date or date field with empty string. If you want to keep date field with empty string then you have to pass NULL if you haven't date value. Hope it may be helpful you.
See, below store procedure.
CREATE PROCEDURE [dbo].[usp_test_date] ( @date date ) AS BEGIN select @date; END
Run with two different way and see result.
1. exec dbo.[usp_test_date] @date = '';
2. exec dbo.[usp_test_date] @date = NULL;
SQL Query to list out all Identity column
Posted by Nirav Patel on June 11, 2011 |
|
Hello
Once I want to get the list of all tables which contains the identity column. After some googling finally I got the sql query which return all the tables which has identity column. It will also return column name, the Seed Values, Increment Values and Current Identity Column value of the table.
SELECT TABLE_NAME,
c.name AS ColumnName,
IDENT_SEED(TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity
FROM INFORMATION_SCHEMA.TABLES
INNER JOIN sys.tables t ON t.name = TABLE_NAME
INNER JOIN sys.columns c ON t.object_id = c.object_id AND c.is_identity = 1
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'
On above query I use INFORMATION_SCHEMA.TABLES, an information schema view which provide an internal, system table-independent view of the SQL Server metadata. 'OBJECTPROPERTY' is a built in sql function which returns information about schema-scoped objects in the current database.
Sql Server Query Performance Tuning
Posted by Bharat Patel on May 09, 2011 |
|
Hello Friends,
Try to avoid function (user defined or inbuild) in where condition of sql query as it is always overlook indexing created on tables and taking much longer time to execute in case of table having large number of rows (over million)
Have a look below query. How to avoid date range condition? I hope it may be helpful to you.
Query 1 : convert(date, getDate())
SELECT * FROM xyz WHERE convert(date, getDate()) = '2011-05-09'
Query 2 : Fixed with differnt way
SELECT * FROM xyz WHERE createddate >= '2011-05-09' AND createddate < dateAdd(d,1,'2011-05-09')
Full text option is greyed out in MSSql 2008
Posted by Pritesh on November 30, 2010 |
|
Today I was trying to setup fulltext indexing on one of my database table. I have finish with creating fulltext catelog and tried to right click on table to create fulltext index but option was greyed out and there is no way to create index.
This is becuse Full-text index was diabled at database leve you can enable it by running below statement.
use [mydatabase name]
go
EXEC sp_fulltext_database 'enable'
That's it and refresh table group and option is enable now
Hope thise
anonymous procedure to measure query performance by averaging
Posted by Rahul on August 14, 2010 |
|
Following code can be used to measure query elapsed time by performing multiple runs of the same query and finding the average elapsed time of the query.
SET NOCOUNT ON DECLARE @ExecutionTime TABLE(Duration INT) DECLARE @StartTime DATETIME,@endTime DATETIME DECLARE @i INT = 1; DECLARE @Iterations int = 5; WHILE (@i <= @Iterations) BEGIN --Force Buffered Data Out For More Accurate Results -- DBCC DROPCLEANBUFFERS SET @StartTime = GETDATE() /* ------ Query To Measure Elaspsed Time ------- */ select myField1 , myField2 ,myField3 from myTable /* ---- End ------- */ SET @endTime = GETDATE() select @i as RunNo , @StartTime as StartTime , @endTime As EndTime , datediff(ms,@StartTime,@endTime) as ElapsedTime INSERT into @ExecutionTime SELECT DurationInMilliseconds = datediff(ms,@StartTime,@endTime) SET @i += 1 END -- WHILE SELECT DurationInMilliseconds = AVG(Duration) FROM @ExecutionTime GO
Reference taken from sqlusa.com
sql server get table list from column name
Posted by Bharat Patel on June 11, 2010 |
|
Hello Friends,
Today, I wanted to list down all table from column name in sql server. Sql server stores all table, column, database or etc in it's own system table. So it's easy to fetch record from it. I have given example below how to list down table list from field name. Copy and paste below code and just pass column name in this query. I hope it is helpful for you and you may like it.
select sys.tables.name from sys.objects inner join sys.tables on sys.objects.object_id = sys.tables.object_id and sys.tables.type = 'U' inner join sys.columns on sys.tables.object_id = sys.columns.object_id where sys.columns.name = 'XYZ' order by sys.tables.name
Temporary Table and Table Variables in SQL Server
Posted by Nirav Patel on May 11, 2010 |
|
Temporary Table
The following code demonstrates how to create a temporary table.
Syntax :
CREATE TABLE #TempEmployee ( employeeID int, employeeName varchar(100) );
-
This table is automatically dropped when the connection session is closed.
-
Foreign key relations cannot be applied to temp tables.
-
Optionally you may drop the table at the end of its use. It is a good practice to drop any temporary table after use.
-
When you create a temporary table it will be created in tempdb. At the time of table creation the tempdb is locked and hence there is some overhead involved using this.
Table Variables
In SQL Server 2000 or higher, one can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory.
The syntax to define a table variable is as follows:
Syntax :
DECLARE TABLE @TempEmployee ( employeeID int, employeeName varchar(100) );
-
Querying table variables is very fast as there are no disk reads needed.
-
Table variables cannot be dropped as they are automatically removed when they are out of scope
-
All the data in table variables is stored in server’s memory. So if there is huge data then it is not recommended to use table variables to avoid memory overhead.
Note : If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won’t create statistics on table variables.
More :
MDSN blog article: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Sql server read excel sheet
Posted by Bharat Patel on May 01, 2010 |
|
Hello Friends,
Today we had a requirement to insert data into database from excel sheet. I used POI Utility to read data from excel sheet and it works fine but problem occurs if the recordset has around thousands of record and request time out error occurs. So I was googling for a solution and came across sql server's in-built function 'OPENROWSET'.Using this function we can read and write excel sheet from sql server.
Follow the procedure below to read or write excel's data using this functionality;
To import Excel file, we need to do following:
1. Put Excel file on server, means we need to put files on server,
if we are accessing it from local.
2. Write following TSQL, to read data from excel file.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls',
'SELECT *
FROM [sheet1$]')
OR Write data from excel sheet.
SELECT * INTO Temp
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Test.xls',
'SELECT *
FROM [sheet1$]') List all databases, objects, tables by a simple single line query.
Posted by Akash Bavlecha on April 27, 2010 |
|
Hello,
Try below query and see all databases and objects get listed in sql server.
sp_msforeachdb 'select "?" AS db, * from [?].sys.tables';
Recent Comments