Friday, January 29, 2010

IF EXISTS for tables

I know - I should have this memorized. And it doesn't deal with schemas.

IF EXISTS
(
SELECT * FROM databasename.dbo.sysobjects
WHERE id = OBJECT_ID(N'tablename')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
DROP TABLE databasename.dbo.tablename

Monday, January 25, 2010

[Setup] Making sure your disks are optimized

At this point, probably everyone knows that you need to make sure to format your drives properly to take full advantage of them. There's two different issues: the cluster size, and the offset.

Practically, you want the offset to be 1024kb (leaves room for SAN "headers"), and the block size to be 64k.

(Link to MS whitepaper, which includes pretty charts showing major improvement: http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx)


Here's how to make sure.

Block size.
c:\users\you> fsutil fsinfo ntfsinfo d:


That will give you a bunch of info. What you care about is the Bytes Per Cluster, which should be 65536 (aka 64k)

Cluster offset:
Using DISKPART:

> diskpart
> list disk
> select disk 1 (or whichever disk you want to look at)
> list partition

Look for the "offset" column

Powershell: See
http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!291.entry
for a powershell script.

To format a drive properly:
diskpart
list disk
select disk 2
create partition primary align=1024
format fs=ntfs unit=64K label="yourdrivenamehere" nowait
exit

Tuesday, January 5, 2010

[Text] Counts of a word within a file. Not by line, total.

Came across this (courtesy of Franklin52 in the unix.com forums).
http://www.unix.com/shell-programming-scripting/63576-how-find-count-word-within-file.html

Say you need a count of a particular word in a file. For example, in an XML file where the word can repeat within a line. Can't use WC or GREP or FIND, but AWK will do the job. (You do have these tools on your Windows box, right? [if you have a UNIX box, it's assumed you do])


awk 'BEGIN{RS=" "}/WORDTOCOUNT/{h++}END{print h}' blah.txt


One note: this assumes that there will be a space somewhere between the occurrence of the words. TESTTEST and
TEST
TEST
would each only count as 1, since there's no space to "reset" the find. (It's a stream function - search for the word. If you find it, increment by one and skip forward to the next space. When you hit a space, start searching again.) For our XML, there are spaces after the tag we searched for, so the counts work.