Sql Server Shrink Log file

 

For a business Intelligence application, we used a sql server 2008 database for consolidating data from different sources, since data was imported, we didn’t need backup facility, so the database was kept in Simple Logged Mode.

In this blog we will see how to shrink the log files for a simple logged database.

IMPORTANT : Use this only for Simple Logged Databases

Use [DatabaseToShrink]-- Replace this with your simple logged database

Declare @FileId Int
select @FileId  = FILE_ID from sys.database_files Where type_desc = 'LOG'

DBCC ShrinkFile(@FileId, 1)

select * from sys.database_files

Reenable sql server index

SELECT
'ALTER INDEX ' + I.name + ' ON ' + T.name + ' REBUILD ' FROM SYS.indexes I INNER JOIN SYS.tables T ON I.object_id = T.object_idWHERE I.name LIKE 'IX%'

Disable all indexes in sql server

For a data marting application being done @ SyneITY, we needed to disable all indexes in our Sql Server 2008 database. We used the following query to get this done

SELECT 'ALTER INDEX ' + I.name + ' ON ' + T.name + ' DISABLE ' FROM SYS.indexes I INNER JOIN SYS.tables T ON I.object_id = T.object_id


WHERE I.name LIKE 'IX%'

Use unpivot to convert columns into rows

In a previous blog we converted rows into columns using pivot, now this time i needed to convert columns into rows. This was simply achieved using the unpivot functionality in sql server.

declare @tbl table(name varchar(50), amt1 numeric(12,2), 
    amt2 numeric(12,2))
    
insert into @tbl(name, amt1, amt2)values('ppv', 1, 3)
insert into @tbl(name, amt1, amt2)values('ppvs', 41, 43)

select * from @tbl 

select * from @tbl unpivot
 ([Amt] for Types in (amt1, amt2)) as unpv

 

 

Credits:

Thanks to Sachin for this!!

Get datetime in YYYYMM format–Sql Server

 

I need to get the date in YYYYMM format i.e. ‘2011-01-23’ becomes ‘201101’

declare @date date = '2016-11-23'
select  convert(varchar(6), @date, 112)

Get Running Total with CTE

 

Declare @Table table (id int identity(1,1), amount numeric(12,2), run_total numeric(12,2))
Insert into @Table(amount) Values( 1000)
Insert into @Table(amount) Values( 2000)
Insert into @Table(amount) Values(4000)
Insert into @Table(amount) Values(4000)
Insert into @Table(amount) Values(6000)
Insert into @Table(amount) Values(3000)


;With CTE_Tbl(id, amount, run_total)
As
(
    select id, amount, amount as run_total 
        from @Table t where id = 1
    union all 
    select t1.id, t1.amount, cast (t1.amount + c.run_total as numeric(12,2)) as rt 
     from @Table t1 
     join CTE_Tbl c on c.id+1 = t1.id    
)
select * from CTE_Tbl

Use CTE & Row_Number to update the Row Number

I had a table where i needed the row number and i had forgotten to insert it. I got this solved using CTE and the Row_Number function.

Declare @test table (name varchar(50), rowno int)
insert into @test (name, rowno) Values ('pramod', 1)
insert into @test (name, rowno) Values ('dileep', 1)
insert into @test (name, rowno) Values ('hari', 1);

with r_SomeTable
as
(
select *
, row_number() over(order by name) as rnk
from @test
)
update r_SomeTable
set rowno = rnk;
select * from @test

Sql Server 2008 Merge sample

Sql server Merge is a great tool as mentioned in my earlier blog. In this blog, i have a better example of using sql server 2008 merge.

If Not Exists(select * from sys.tables where name = 'TestTable')
    Create Table TestTable(Id Int, SerialNo Int, Notes Varchar(50))

Declare @Tbl Table(SerialNo Int, Notes Varchar(50))
begin tran

Insert into TestTable(Id, SerialNo, Notes)
    Values(1, 1, 'shouldnot be affected')
Insert into TestTable(Id, SerialNo, Notes)
    Values(1, 2, 'shouldnot be affected')
    
    
Insert into TestTable(Id, SerialNo, Notes)
    Values(2, 1, 'to-delete')
Insert into TestTable(Id, SerialNo, Notes)
    Values(2, 2, 'to update')
    
    
Insert into @Tbl(SerialNo, Notes)
    values(3, 'to insert')

Insert into @Tbl(SerialNo, Notes)
    values(2, ' updated value')

select * from TestTable
Declare @Id Int = 2

Merge TestTable as Target
Using @Tbl as Source
    On Target.Id = @Id And Target.SerialNo = Source.SerialNo
    When Matched Then Update Set Target.Notes = Source.Notes
    When Not Matched By Target Then Insert(Id, SerialNo, Notes)
        Values(@Id, SerialNo, Notes)
    When Not Matched By Source And Target.Id = @Id Then Delete
    ;

-- all items with id 1 must be present
select * from TestTable
rollback

Implement paging with CTE and Row_Number in Sql Server.

We needed to implement paging and this is how we at SyneITY went about it. Since we were using Sql Server 2008 we decided to go for using the Row_Number() function along with CTE (Common Table Expression).

Procedure

Using Merge in Sql Server 2008

Credits:

I came to know about merge through my friend, my colleague and my partner Gireesh. Thanks for the effort he put in.

 

The scenario for using Sql Server 2008 Merge?