|
|
-
|
|
When you use sql_variant datatype in sql server 2000, and add data to a table
using union all, it will work though datatype of data differs from each other
declare @test table(variant_column sql_variant)
insert into @test
select 'test' union all
select 345 union all
select getdate() union al......
|
|
-
|
|
Sometimes when you import data from other system, you may need to clean the data by removing unwanted data.
Consider you import data where you want to keep only those which are purely alphanumerals. Here is a way to find out
declare @table table(data varchar(20))
Insert into @table
select......
|
|
-
|
|
When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions
There are many methods to know the Compatibility Level of a database using queries
--Method 1
EXEC sp_dbcmptlevel your......
|
|
-
|
|
Set the result mode to text and run this query
set nocount on
select space(7-len(replicate(char(42),no)))+ replicate(char(42),no*2-1) from
(
select top 5 row_number() over (order by name) as no from sysobjects
) as t
union all
select space(5)+replicate(char(124),3)
union all
selec......
|
|
-
|
|
You know that NULL values on joined columns are omitted from comparision when tables are joined
Consider this example
Declare @t1 table(col1 int, col2 varchar(10))
insert into @t1
select 1, 'test1' union all
select 2, 'test2' union all
select NULL,'test3' union all
select 5,' test4'
......
|
|
-
|
|
How do I view the script of the DDL triggers?
The answer is not
sp_helptext 'DDL trigger'
Because DDL triggers are defined at database/server level and not at any table level
So they are not stored in a system objects like sys.objects and the usage of sp_helptext,object_name, etc wont wor......
|
|
-
|
|
Row_number() function is used to generate row number to rows based on the column which is ordered
What if you want to generate row number without ordering any column
Here is the method (Consider Suppliers table from northwind database)
1 Assign dummy column with literal 0 and order it by th......
|
|
-
|
|
=60 then cast(@sum*100 ......
|
|
-
|
|
If you want to know the datatype or length of a column, you can query on the system table syscolumns or system view INFORMATION_SCHEMA.COLUMNS
But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function
Example
SELECT
TOP 1
SQL_VARIANT_PROPERTY('col......
|
|
-
|
|
In the post Import/Export to Excel, I showed how to export data to EXCEL
The problem that most users find it is it wont include column names in the file and file should exists already with headings
This procedure would solve that problem
create procedure proc_generate_excel_with_columns
(
......
|
|