how to retriev the first 10 records from table in sql server with out using top key word( duplication should not be find)
select distinct a.questionid,a.question,a.questiongroup from questions a, questions b where a.questionid>=b.questionid group by
a.questionid,a.question,a.questiongroup
having count(*)<=10
select distinct a.* from table1 a, table1 b where a.id>=b.id group by
a.id,a.col1,a.col2
having count(*)<=10
=============================================================================================================
How can I tell whether a table is a system (Microsoft) table in SQL 7?
select * from sysobjects where objectproperty(id, 'IsMSShipped') = 1
=============================================================================================================
Master DB
It holds all the info reg the databases available in that server.
It also holds the information abt physical paths,the various groups of mdf,ldf
files of the databases.
TempDB
It is used for creating/storing the temporary tables /stored procedures
used in the stored procedures.
If the SQL server is restarted then all the tables /stored procedures are
cleared and the entire db is reintialized.
MSDB
It is used by SQL Server Agent for scheduling jobs.
To store the information reg the Restore/Backup and the devices used for
Restore/Backup ,the location of the backups etc
=============================================================================================================
All of the available collations can be returned by using the
fn_helpcollations() function, for example:
SELECT * FROM ::fn_helpcollations()
-- Case Insensitive compare
SELECT *
FROM crmusers
WHERE UserPassword = 'A' COLLATE SQL_Latin1_General_CP1_CI_AS
-- Case Sensitive Compare
SELECT *
FROM crmusers
WHERE UserPassword = 'a' COLLATE SQL_Latin1_General_CP1_CS_AS
=============================================================================================================
-- To get identity columns from a table
SELECT @IdentityColumnName = Name FROM syscolumns WHERE status = 128 AND
ID=object_id(@TableName)
=============================================================================================================
-- to get Identity column details such as seed and current increment value
select ident_seed(name),ident_incr(name) as seed ,name,id,xtype from syscolumns where ident_seed(name) is not null
-- to reset the identity seed
DBCC CHECKIDENT('leads63', RESEED, 1)
To set Identity seed to any number use this following command
DBCC CHECKIDENT('tablename', RESEED, 10)
'Tablename is table name which you want to reseed and 10 the start seed for the
table means it start from 10 it inserts next record with id 11
,12,...
-- to set identity insertion on , off
SET IDENTITY_INSERT Yaks ON
Insert Yaks (YakID, YakName) Values(1, 'Mac the Yak')
SET IDENTITY_INSERT Yaks OFF
select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like 'leads%'
=============================================================================================================
Some SQL Queries
Here are some useful sql queries
How to Run commands for all tables in current dB:
EXEC sp_MSforeachtable
@command1 = 'DELETE FROM ? WHERE your_condition'
The '?' will be replaced by the table name
--------------------------------------------------------------------------------
How to find Size of all user tables with the number of rows:
EXEC sp_MSforeachtable
@command1='sp_spaceuse d "?"'
--------------------------------------------------------------------------------
How to find Number of rows in a table and the space the table and
index use (in KB):
EXEC sp_MStablespace table_name
--------------------------------------------------------------------------------
How to display Size of the datafile and log separately:
EXEC sp_helpdb database_name
--------------------------------------------------------------------------------
How to List of all hard drives and the amount of free space in MB
for each drive:
EXEC master..xp_fixeddrives
--------------------------------------------------------------------------------
How to return all tables, which contain a specified column:
SELECT a.name 'Table'
FROM sysobjects a
JOIN syscolumns b
ON a.id = b.id
WHERE a.type = 'U' AND
b.name = + 'your_column'
--------------------------------------------------------------------------------
How to Delete a file from the server in SQL:
xp_cmdshell "cmd /c del filename_with_path "
--------------------------------------------------------------------------------
How to Control State Options of the Database:
ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK AFTER x SECONDS
or
ALTER DATABASE db_name
SET RESTRICTED_USER
(or MULTI_USER)
WITH ROLLBACK IMMEDIATE
(Roll back transactions after the specified number of seconds or
immediately.)
=============================================================================================================
=
You often need a bulk insert with dynamic primary key where you can define your keys without getting it from the .txt files or .csv files.
here are the stored procedure to bulk insert country with dynamic primary key.
first create temp.txt file in c drive with following data.
Australia
Canada
America
India
then create table with following fields
countryid int 4
countryname varchar(100)
and assign name as tblCountryMaster
now use following stored procedure
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_BulkInsertCountry
(
@FilePath varchar(1000)
)
AS
BEGIN--PROCEDURE
--variable declaration
declare @SQL varchar(500)
declare @id int
declare @CountryName varchar(30)
--Create temporary table for Country
CREATE TABLE #tmpCountry
(
CountryName varchar(30),
)
---executing bulk insert on temporary table
SET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH
(FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')'
EXEC(@sql)
DECLARE cursor_Country CURSOR READ_ONLY FOR
select [CountryName] from #tmpCountry
OPEN cursor_Country
FETCH NEXT FROM cursor_Country INTO @CountryName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @id=isnull(max(Countryid),0) from tblCountryMaster
SET @id=@id+1
INSERT INTO tblCountryMaster values(@Id,@CountryName)
FETCH NEXT FROM cursor_Country INTO @CountryName
END
CLOSE cursor_Country
DEALLOCATE cursor_Country
END--PROCEDURE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
happy programming
=============================================================================================================
=
--Gives the new empty record
select * FROM Customers WHERE CustomerId = 'NewID'
=============================================================================================================
=
to display the field like 'Relation'. means first letter should be a uppercase
and
remaining letters are lower case. to use this sql query to get from DB.
SELECT UPPER(LEFT(Column_Name, 1))+LOWER(SUBSTRING(Column_Name, 2,
(LEN(Column_Name) - 1))) FROM List
=============================================================================================================
=
Q How can I list out database tables which have no records?
Ans.
declare @strsql varchar(256)
create table #emptytables (tablename varchar(128), table_rowcount int)
select @strsql='select distinct o.name as TableName, x.rowcnt as
Table_RowCount
from sysobjects o
inner join sysindexes x
on o.id = x.id
where x.rowcnt = 0 and
o.type = ''U'''
insert #emptytables (TableName, Table_rowcount) exec (@strsql)
select * from #emptytables
drop table #emptytables
=============================================================================================================
=
Question:-How do you programmatically find out when the SQL Server service
started?
Answer:-There is one simple but awkward method is there to find out when the SQL server service is started.
The following query will give you the time when the SQL server service is started;
select crdate from [master].dbo.sysdatabases
where name=’tempdb’
This is because, the tempdb will be created whenever SQL server service is
started or restarted.
=============================================================================================================
=
EXEC sp_helptext vwProductSales
--This stored procedure is used to get the depends details means reference key,
foreign keys etc...
EXEC sp_depends vwProductSales
EXEC sp_depends products
=============================================================================================================
=
select rows from 5 to 15 without id coloumn
First take top 15 records and minus top 5 records.
select top 10 * from Emp where eno not in(select top 5 eno from Emp)
=============================================================================================================
=
gives percentage of rows
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the
number of rows to return. If PERCENT is specified, n is the percentage
of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */
TOP 15 PERCENT /* Return the top 15% of the result set. */
=============================================================================================================
=
To select duplicate row count from the table
select count(name),name from list
group by name
having count(name) > 1
to delete duplicate rows from the table
delete from list where (id) not in (select min(id) as id from list group by
name)
to delete without id table
SET ROWCOUNT 1 DELETE testing
FROM testing a
WHERE (SELECT COUNT(*)
FROM testing b
WHERE b.id = a.id AND b.name = a.name) > 1 WHILE
@@rowcount > 0 DELETE testing
FROM testing a
WHERE (SELECT COUNT(*)
FROM testing b
WHERE b.id = a.id AND b.name = a.name) > 1
SET ROWCOUNT 0
-- to delete without id table by Checking two fields id and title
SET ROWCOUNT 1
DELETE JOB FROM JOB a
WHERE (SELECT COUNT(*) FROM JOB b
WHERE b.id = a.id AND b.TITLE = a.title) > 1
WHILE @@rowcount > 0
DELETE job FROM job a
WHERE (SELECT COUNT(*) FROM job b
WHERE b.id = a.id AND b.title = a.title) > 1
SET ROWCOUNT 0
-- to delete without id table by Checking only one field title
SET ROWCOUNT 1
DELETE JOB FROM JOB a
WHERE (SELECT COUNT(*) FROM JOB b
WHERE b.TITLE = a.title) > 1
WHILE @@rowcount > 0
DELETE job FROM job a
WHERE (SELECT COUNT(*) FROM job b
WHERE b.title = a.title) > 1
SET ROWCOUNT 0
=============================================================================================================
=
--To get the comma seperated string from a table
Declare @str Varchar(100)
-- Forming the comma separated string
Select @str= COALESCE(@str+',' , '') + Val from vin_temp
Select @str
=============================================================================================================
=
--To get stored proecedure detail using query we can use this command
SELECT OBJECT_DEFINITION (OBJECT_ID (' < name of SP > ') )
where <name of SP> is Stored Procedure Name (SQL 2005)
=============================================================================================================
=
To transfer data from one server to another server by using Query
1) First Execute the Stored procedure
sp_addlinkedserver <ServerName>
e.g. sp_addlinkedserver Ravi1
2) Then execute the query
Select * from Server.Database.Owner.Table
e.g. select * from Ravi1.Stars_db.dbo.validsurveys
=============================================================================================================
=
-- select data from remote server on the internet using ip adress
use [ipaddress].database_name.dbo.table_name
1) sp_addlinkedserver [remoteserver Name]
2) Select ID from TableName
Where ID not in (Select ID from
[20.10.0.233].Database.dbo.TableName)
Order to run the query
Run the 1) query
Run the 2) query.
=============================================================================================================
=
Selecting nth highest salary in bellow query change the number
6 to any position then it gives highest salary of that position -1
means if it 6 then it gives 7th highest salary
if it 4 then it gives 5th highest and so on.
select salary from emp e
where 6 = ( Select count(distinct(sal)) from emp e1 where e.sal <
e1.sal)
-- selects nth highest salary bellow ine picks 6th highest salary
select Top 1 sal from emp where sal in(Select Top 6 sal from emp order by desc)
order by sal ASC
select MIN(sal) from emp where sal in(Select Top 6 sal from emp order by desc)
--We can get random records every time by using this query
select top 5 * from answer order by newid()
=============================================================================================================
=Microsoft introduced the EXCEPT operator in SQL Server 2005, which returns all of the distinct rows from the left side of the EXCEPT operator. It
also removes all of the rows from the result set that match the rows that are on the right side of the EXCEPT operator.
eg:
SELECT C1,C2 FROM T1 EXCEPT SELECT C1,C2 FROM T2
SELECT C1,C2 FROM T2 EXCEPT SELECT C1,C2 FROM T1
SELECT c1,c2 FROM t1 INTERSECT SELECT c1,c2 FROM t2
=============================================================================================================
=-- selecting all column names from all tables
Select TABLE_CATALOG = Left(TABLE_CATALOG, 10),
TABLE_SCHEMA = Left(TABLE_SCHEMA, 10),
TABLE_NAME = Left(TABLE_NAME, 10),
COLUMN_NAME = Left(COLUMN_NAME, 20)
From stars_db.Information_Schema.Columns
Where Table_Name like 'S000%'
Select TABLE_CATALOG ,
TABLE_SCHEMA,
TABLE_NAME ,
COLUMN_NAME
From stars_db.Information_Schema.Columns
Where Table_Name like 'S000%' AND COLUMN_NAME LIKE 'ValidC%'
=============================================================================================================
=select
1 A
2 B
3 C
like A,B,C........
declare @n varchar(1500)
set @n=''
select @n=@n + convert(varchar(10),QuestionID) + ',' from answer
select @n
declare @str nVARCHAR(4000)
select @str = COALESCE(@str+',','Start ') + convert(varchar(10),QuestionID)
from answer
select @str
=============================================================================================================
=-- To get Table Created and modified date
Select name,create_date,modify_date From sys.tables
=============================================================================================================
http://sscheralbidar.blog.co.uk
http://sscheral.atwiki.com
http://sscheral.spaces.live.com/
=============================================================================================================
In-Built Functions
AT(): Returns the beginning numeric position of the nth occurrence of a
character expression within another character expression, counting from
the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a
character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within
another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within
another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters
to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters
to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters
to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a
character in a second character expression with the corresponding
character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second
character expression, and then replaces each occurrence with a third
character expression. Unlike a built-in function Replace, STRTRAN has three
additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as
appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right
search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified
numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman
numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
=============================================================================================================
=inbuilt functions
SELECT LEFT(LastName, 3) AS FirstThreeLettersOfLastName FROM Employees;
SELECT RIGHT(LastName, 2) AS LastTwoLettersOfLastName
FROM Employees
SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM Employees
SELECT SUBSTRING(REVERSE(LastName), 3, 4) AS
PortionOfLastNameMirrorImage
FROM Employees
SELECT REPLACE(Title, 'Sales', 'Customer Service') AS ManipulatedTitle,
Title
FROM Employees
SELECT REPLICATE('MyCoolString', 5)
SELECT SPACE(12) AS Spaces
SELECT UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2,
(LEN(FirstName) - 1))) + ' '
+ UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, (LEN(LastName) -
1)))
AS FullName
FROM Employees
SELECT 'My Output' + CHAR(10) + CHAR(13)
+ 'AnotherOutput'
=============================================================================================================
=www.TransactSQL.Com