Delete headers in Word Documents

I used Red Gate Trial Version to Generate my SQL server database Documentation and I Need to remove the automatic generated Header and Footer by the software and this saved my day.It isnt that hard to be a DBA. πŸ™‚
I used following in the VBA and saved my self from 2000 page document.



Sub RemoveHeadAndFoot() Dim oSec As Section Dim oHead As HeaderFooter Dim oFoot As HeaderFooter For Each oSec In ActiveDocument.Sections For Each oHead In oSec.Headers If oHead.Exists Then oHead.Range.Delete Next oHead For Each oFoot In oSec.Footers If oFoot.Exists Then oFoot.Range.Delete Next oFoot Next oSec End Sub

Knowing your indexes


This following query gives you
list of all the indexes
,the tables that they reside in
,how many times they’re been searched
,how many times they’ve been updated
,and the size of the indexes in memory.


SELECT 
	sch.name + '.' + t.name AS [Table Name], 
	i.name AS [Index Name], 
	i.type_desc,  
	ISNULL(user_updates,0) AS [Total Writes], 
	ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads],
	s.last_user_seek, 
	s.last_user_scan ,
	s.last_user_lookup,
	ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference],  
	p.reserved_page_count * 8.0 / 1024 as SpaceInMB
FROM sys.indexes AS i WITH (NOLOCK)  
	LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s	WITH (NOLOCK) ON s.object_id = i.object_id  AND i.index_id = s.index_id  AND s.database_id=db_id()  AND objectproperty(s.object_id,'IsUserTable') = 1  
	INNER JOIN		sys.tables					AS t	WITH (NOLOCK) ON i.object_id = t.object_id  
	INNER JOIN		sys.schemas					AS sch	WITH (NOLOCK) ON t.schema_id = sch.schema_id  
	LEFT OUTER JOIN sys.dm_db_partition_stats	AS p	WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id
WHERE (1=1)
	--AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used  
	--AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used  
	--AND i.index_id > 1			-- Only non-first indexes (I.E. non-primary key)
	--AND i.is_primary_key<>1		-- Only those that are not defined as a Primary Key)
	--AND i.is_unique_constraint<>1 -- Only those that are not classed as "UniqueConstraints".  
ORDER BY [Table Name], [index name]

Source : Internet

I should’ve looked the other way

I am feeling exactly the same as Rob Farley must have felt πŸ™‚

Β© 2011 Rob Farley

Verse 1:
My query sucks – it takes too long
So long I wrote this song
The plan’s not big – it ain’t a giant
And yet I have an angry client
Performance now has made her weary
So I’ve come in to fix her query
I promise I won’t ever fail her
Say “Trust me, love, I’m from Australia!”

Chorus:
I need to find you
But I don’t want to search every row
My predicate’s residual
My seek just runs too slow
I thought I’d caught a glimpse of you
Been searching for all day
But all along, I’d done it wrong
I should’ve looked the other way

Verse 2:
A trace is on, I know the reads
That fetch the bytes the query needs
There’s spooling from a CTE
They’ve got recursion needlessly
I need to dig a little further
I worry there might be a cursor
The DBA has the plan_handle
He says it’s not corrupt, he knows Paul Randal!

Repeat chorus

Bridge:
There is an index covering predicates with keys
But my developer has used inequalities
There is a range scan
Hiding truth
Hiding cost
Hiding you…

Repeat chorus x2

I should’ve looked the other way

Temp table caching table valued parameters in SQL Server 2008 /R2

When you use Table Valued Parameters,TVP, SQL Server internally uses temp table to store the data. Temp tables can be cached for re-used. Caching reduces contentions such as page latch contentions on system tables which can occur as temp tables are created and dropped at a high rate.
If you use TVP with a stored procedure, temp table for the TVP will be cached in SQL Server 2008 while using TVP.
But if you use TVP together with parameterized queries, temp tables for TVP won’t be cached in SQL 2008 or 2008 R2. This leads to page latch contentions on system tables.

USE of COUNT()

We all use COUNT() to count the total records in a table.But there are few facts we all unknowingly ignore.

In the example below I have use COUNT(*), COUNT([tableField]) and COUNT(1)

COUNT(*) does return the count of all the records, BUT takes lot of memory and time when we have a big table with huge data.

BEWARE
COUNT([tableField]) does return the count but when the field has NULL as data value the,it escapes to count those records because the field is NULL.
So always use the Key column.

SO LETS USE
COUNT(1)
Here 1 = the first column of the table-key column which is always the key column of any table
1) It takes into account the NULL value.
2) It saves time and memory.

——————————————————————————–
Create table tblCountTest
( fldCountryName varchar(10));

insert into tblCountTest values (‘Nepal’);
insert into tblCountTest values (‘China’)
insert into tblCountTest values (NULL);
insert into tblCountTest values (”);
insert into tblCountTest values (‘Cambodia’);
insert into tblCountTest values (NULL);
insert into tblCountTest values (‘Japan’);
insert into tblCountTest values (‘Brazil’);

select * from tblCountTest
select COUNT(*) as recordCount FROM tblCountTest; — shows recordCount = 8
select COUNT(fldCountryName) as recordCount FROM tblCountTest; — shows recordCount = 6
select COUNT(1) as recordCount FROM tblCountTest; — shows recordCount = 8
————————————————————————————
Happy Coding πŸ™‚

I dont feel safe

I just got a notification from google “Google Terms of Service Updated” and I am having an mini anxiety attack.I am wondering who will be scrutinizing my information, my emails, my stuffs, how will the data be used, abused and sold and resold.I wish I could declare,ok I wont use this thing called internet,I will shut down everything.Even-if I decide to,I am already on (their) data-centers,a considerable amount of me is already with them.And I cannot tell them I don’t want to use you please spare me.

I am all scattered in web, part of me lies in facebook other half on twitter and I am tits and bits all over in different social networking sites.A chunk of me lies in sugarsync,dropbox,where not.Yes I don’t feel secure, there are cloud servers I use, I have had synced me with them, my personnel belongings, professional journals, my secret indulgings what not.Yes I am scattered everywhere.I so am floating in fearful concern of my interest,Phew!!

I have been having this feeling way before Mr Snowden leaked Prism.I sometimes wonder if using TOR or disconnect will save me from being crawled and tracked to some extent. Again I question Would not these so called anti surveillance or anti tracking companies use my informations.Would not it be same in one way or other?I let other bunch of them use me, just in different way.

when I read about the Silk Road, it was so intriguing;I wondered how things worked and fantacise of building something similar.Wont it be amazing to have something of my own: no surveillance, no crawling, no bots, a world of my own, where my data would be safe and not employed for filling up other’s pocket

Nothing is for free I get it,so this is the sum amount I need to pay for using things for free but isn’t this amount we are paying a bit too much? I am solding myself off to get a bar of candy for free.This is an abuse.We are being abused and this has lead each one of us to live in threat, each single moment. Some dignitaries live in fear-fear of being public,affairs being barefaced.And a normal person like me fear of being tagged as a republican or supporter of Mao ,Zeehad or anything, on basis of the conversations I make on instant messaging or my emails or social media activities which is absurd.I like Manchester United.Will that make me a anti Chelsea supporter when I am teasing my friend with anti Chlesea slogans?

I think we all need to wake up and start thinking about these issues.

How can we live like this?

This is against my rights to security, rights to liberty,my rights to live a life.