Thursday, October 11, 2007

Serp Archive

What is SerpArchive?
SerpArchive is a service which daily stores the results of search engine queries indicated by user.

Using this service users are able:
  • to track the dynamic changes of the results of search engine queries
  • to follow the rating changes of your resources and the resources of your business rivals in real time
  • to analyse the changes in rating algorithm of search engines
  • to track advertising programmes of Google Adsence
  • to track down sites which appear/disappear from SERPs
  • to analyse the dynamic changes of Google PR
  • to follow the history of changes of Google cache in time
SerpArchive

Wednesday, October 10, 2007

Internet names for Asia launched

The .asia regional internet domain has officially opened for business, with big firms expected to grab addresses.

Governments and companies can now register interest in specific domain names, such as www.namehere.asia.

Companies will be able to register domains for which they own a trademark and governments will get a chance to earmark those on a reserved list.

The general public will get a chance to snap up their own .asia domain when the landrush starts in February 2008.

BBC News

MS SQL server: How to dynamically number rows in a SELECT T-SQL statement

set nocount on
if exists (select * from sysobjects where type = 'U' and name = 'ranked_table')

begin
drop table ranked_table
end

-- create a test table (just for example)
create table ranked_table
(
id_test int identity not null,
string varchar (255),
constraint pk_test primary key (id_test)
)


set identity_insert ranked_table on

-- insert sample data
insert into ranked_table (id_test, string)
values (1,'testdata1')

insert into ranked_table (id_test, string)
values (3,'testdata2')
insert into ranked_table (id_test, string)

values (11,'testdata3')
insert into ranked_table (id_test, string)
values (45,'testdata4')

insert into ranked_table (id_test, string)
values (58,'testdata5')
insert into ranked_table (id_test, string)

values (61,'testdata6')
insert into ranked_table (id_test, string)
values (111,'testdata7')

insert into ranked_table (id_test, string)
values (123,'testdata8')
insert into ranked_table (id_test, string)

values (134,'testdata9')

set identity_insert ranked_table off
go

-- 1. Create a View
if exists (select * from sysobjects where type = 'V' and name = 'ranked_view')

begin
drop view ranked_view
end
go
create view ranked_view (rank, id_test, sting)

as
select (
select count(*)
from ranked_table as test_2
where test_2.id_test <= test_1.id_test
) as rank,
test_1.id_test,
test_1.string
from ranked_table as test_1

go
select *
from ranked_view
order by rank
go


-- 2. "Standard" T-SQL
select count (test_2.id_test) as rank, test_1.id_test, test_1.string
from ranked_table as test_1 inner join ranked_table as test_2 on

test_1.id_test >= test_2.id_test
group by test_1.id_test, test_1.string
order by rank
go

-- 3. "Standard" T-SQL
select test_3.rank, test_3.id_test, test_3.string
from (select test_1.id_test,
test_1.string,
(select count(*)
from ranked_table as test_2
where test_2.id_test <= test_1.id_test
) as rank
from ranked_table as test_1) as test_3

order by rank
go

-- 4. Temporary table with IDENTITY
if not object_id('tempdb..#rank_table') is null
begin
drop table #rank_table
end
create table #rank_table
(
rank int identity not null,
id_test int null,
string varchar (255),
constraint pk_test primary key (rank)
)
go
insert into #rank_table (id_test, string)
select id_test, string
from ranked_table
order by id_test

select * from #rank_table
go

-- 5. Table variable with IDENTITY
declare @rank_table table
(
rank int identity not null,
id_test int null,
string varchar (255)
)

insert into @rank_table (id_test, string)
select id_test, string
from ranked_table
order by id_test
select * from @rank_table

go

-- 6. Cursor
declare @rank int,
@id_test int,
@string varchar (255)

declare rank_cursor cursor
for select id_test, string
from ranked_table
order by id_test

open rank_cursor
fetch next from rank_cursor into @id_test, @string
set @rank = 1

while (@@fetch_status <> -1)
begin
select @rank, @id_test, @string
set @rank = @rank + 1
fetch next from rank_cursor into @id_test, @string

end
close rank_cursor
deallocate rank_cursor


Tuesday, October 9, 2007

MS SQL server: E-mail address validation

if @email not like '[^.]%[^.]@[^.]%[^.].[^.]%[^.]'
or @email like '%[а-я `!#$%^&*(){}\|/,?]%'
or isnull(charindex('@', substring(@email, charindex('@', @email) + 1, len(@email) - charindex('@', @email))), 1) > 0
print 'Not valid'
else
print "valid'

MS SQL server: How can I do a case-sensitive comparison on a SQL Server installed with a case-insensitive sort-order?


select * from my_table
where convert(varbinary(4), my_column) = convert (varbinary(4), 'teST')