|
|
-
|
|
NULL. This is a magical word in Database programming. Here are some interesting facts about NULL in SQL Server.
1 NULL can be defined as absense of value, undefined, or the value which is unknown at this point of time.2 All datatypes can be defined with...
|
|
-
|
|
My Co-worker complained me that AVG function is not properly working in SQL Server whereas it works correctly in mysql. I immediately told him that AVG does the implicit convertion by default and the result may be wrong (based on datatype).
Consider th...
|
|
-
|
|
In SQL forums, one of the memebers asked this question. "In the table a varchar column has values like '2.2020 30 4.0000', '2.20200 30 4.00', etc. When I pass a parameter with the value '2.202 30 4.0' , the above value should be returned as output. How ...
|
|
-
|
|
Consider the following two statements
--Query 1 select
name,
dateadd(day,datediff(day,0,create_date),0) as date_only
from
sys.objects
where
date_only>getdate()-100
--Query 2 select
name,
dateadd(day,datediff(day,0,create_date),0) as...
|
|
-
|
|
My friend told me that in an interview he was asked to write a code that sums the digits of the number. He asked me if there is a way of doing it without using a WHILE loop. Here are two methods
Method 1 : Dynamic SQLdeclare @i int, @sql varchar(8000)
s...
|
|
-
|
|
One of my collegues told me that while we cannot use a temporary table created inside dynamic sql out of it, in a similar way it is not possible inside dynamic sql to access a temporary table created out of dynamic sql. How many of you think this is tru...
|
|
-
|
|
Well. You are aware of Computed Columns in SQL Server. In a similar way you can also use Computed Parameters. Consider the following procedure. Create procedure test
(
@datetime datetime,
@date date = @datetime
)
as
select @datetime as date_with_time...
|
|
-
|
|
In this series at Part 1 Removing unwanted characters , I posted a method that used a function. In part 2 I posted a method that used Dynamic SQL. Here is another improved method that uses Dynamic SQL
--Create test data
create table #data (data varchar(100))
insert #data
select 'tes^@&t......
|
|
-
|
|
Run the following code
declare @t table(i int unique)
insert into @t
select 1 union all
select null union all
select null
You will get the following error
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UQ__#A989971__3BD019979D26A3BB'. Cannot insert duplica......
|
|
-
|
|
Did you know that table variables are stored in the tempdb database for execution scope only?. Ok. Let us run this code
declare @t table(i int)
select * from tempdb.INFORMATION_SCHEMA.TABLES
The result is
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
--------------------......
|
|