|
|
-
|
|
I have seen many newbies asking "How do I sort the numbers stored in varchar columns?"
Here are some methods
declare @t table(data varchar(15))
insert into @t
select '6134' union all
select '144' union all
select '7345' union all
select '109812' union all
select '100074'union all
sele......
|
|
-
|
|
As you know, Replicate function is used to repeat a character expression for a specified number of times.
But by default the result is converted to varchar of maximum size 8000 when you dont convert the expression to specific type
Consider the following example
declare @v varchar(max)
set @v=......
|
|
-
|
|
Temporary database tempdb is created whenever the Server is restarted.
So you can know when Server was restarted by knowing the creation date of the tempdb
EXEC sp_helpdb tempdb
select crdate from master..sysdatabases
where name='tempdb'
......
|
|
-
|
|
@database_id)
Begin
select @dbname=name,@database_id=database_id from sys.databases where database_id=@database_id
EXEC('SELECT * FROM '+@dbname+'.INFORMATION_SCHEMA.TABLES where table_name='''+@table_name+'''')
select @database_id=min(database_id) from sys.databases wh......
|
|
-
|
|
In addition to INFORMATION_SCHEMA VIEWS, in SQL Server 2005, we can use Object Catalog Views to know more informations about the objects
Refer this http://msdn.microsoft.com/en-us/library/ms189783.aspx......
|
|
-
|
|
Sometimes you may need some sample data for testing purpose
The following may help you in generating some sample data of different datatypes
select
abs(checksum(newid()))%10000 as intcol,
abs(checksum(newid()))*rand()/100 as float_col,
dateadd(day,0,abs(checks......
|
|
-
|
|
Well. Most of you know the diffrence between the two.
http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/
http://www.codethinked.com/post/2007/11/UNION-versus-UNION-ALL2c-lessons-in-minutiae.aspx
Here is one of the differences that is not kn......
|
|
-
|
|
Suppose you have string in csv format and want to split into seperate columns. You can use parsename function as long as you have maximum four values. The following would work for any number of values
declare @s varchar(2000),@data varchar(2000)
select @s='this,is,test'
select @data=''''+......
|
|
-
|
|
You can use one of the following to know the structure of a table
Generate SQL Script option from Enterprise Manager/Management Studio
select * from information_schema.columns where table_name='table_name'
EXEC sp_help 'table_name'
EXEC sp_columns 'table_name'
In Query Analyser type......
|
|
-
|
|
This is for newbies who struggle to understand how single quotes work in SQL Server I have seen newbies worrying why the following doesnt work
SELECT columns from mytable where col ='Lifco's'
When you specify a value which has single quote, you need to double it
SELECT columns from myta......
|
|