Replace function in sql server

The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:

Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.

Assume we have the following table:

Table Geography region_name store_name
East Boston
East New York
West Los Angeles
West San Diego


If we apply the following Replace function:

SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

Result: region_name
Eastern
Eastern
West
West

case statement in select query in sql server

A special scalar expression in SQL language is CASE expression. SQL CASE expression is used as a kind of IF-THEN-ELSE statement. It is similar to switch statement in modern programming language such as Java or C#. The syntax of the CASE statement is simple as follows :






SELECT name,salary,  
CASE      
WHEN  salary <= 2000 THEN 'low'
WHEN  salary > 2000 AND salary <= 3000 THEN 'average'
WHEN  salary > 3000 THEN 'high'
END AS salary_level  
FROM employees  
ORDER BY salary ASC

select columns name from table schema in sql server

select COLUMN_NAME from information_schema.columns where table_name='TRGToken'

Shrink database of sql server 2005

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

Shrink Virtual Log files in sql server 2005

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO



Again, here I have assumed that your initial log size is 1 GB, but in reality you should select the number based on your own ideal size of the log file. If your log file grows to 10 GB every day, you may want to put the value as 10 GB.

Shrink Database in sql server

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[spShrink]
as
Begin
backup log kamaononstop with truncate_only
dbcc shrinkdatabase (kamaononstop,10,truncateonly)

ALTER DATABASE kamaononstop
MODIFY FILE ( NAME = N'kamaononstop',
MAXSIZE = 1024000KB , FILEGROWTH = 50%)

ALTER DATABASE kamaononstop
MODIFY FILE ( NAME = N'kamaononstop_log',
MAXSIZE = 1024000KB , FILEGROWTH = 50%)

END

sql server join with example

create PROCEDURE dbo.join_test
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
begin


declare  @tbl1   table (id numeric(10),[name] varchar(50))

declare  @tbl2   table (id numeric(10),[name] varchar(50) )

insert into @tbl1 values(1,'a')
insert into @tbl1 values(2,'b')
insert into @tbl1 values(3,'c')
insert into @tbl1 values(4,'d')
insert into @tbl1 values(5,'e')

insert into @tbl2 values(1,'p')
insert into @tbl2 values(2,'q')
insert into @tbl2 values(6,'r')
insert into @tbl2 values(7,'s')
insert into @tbl2 values(8,'t')

-- select a.id , a.name,b.id,b.name  from @tbl1 as a left outer join  @tbl2 as b on a.id=b.id

-- select a.id , a.name,b.id,b.name  from @tbl1 as a right outer join  @tbl2 as b on a.id=b.id

-- select a.id , isnull (a.name,'no name'),b.id,b.name  from @tbl1 as a full outer join  @tbl2 as b on a.id=b.id

-- select a.id , a.name,b.id,b.name  from @tbl1 as a full outer join  @tbl2 as b on a.id=b.id where a.id is null or b.id is null

-- select a.id , a.name,b.id,b.name  from @tbl1 as a cross join  @tbl2 as b where a.id=b.id

end