Quirk in SQL Server to do with sizing tempdb that I ran into today: SQL Server checks the current location when you move tempdb to see if there is enough space not the new location.
From: Configuring Database Files for Optimal Performance
The below script will move TempDB from its current location to a folder on the T drive. Change the drive letter and folder location to suit your system. The script only uses a 1gb file size because of an odd behavior in SQL Server that checks the current file location to see if there’s enough space - instead of checking the new file location. If the user specifies a 100gb TempDB data file on the T drive (which does have 110gb of free space), SQL Server checks the current location (C) for 100gb of free space. If that space doesn’t exist, the script will fail. Therefore, use a small 1gb file size first, then after SQL Server restarts, alter the file to be the full desired size.
sqlserver
Coding for fun not profit.
#!/d/perl/bin/perl.exe
# (void*)
# If I ever own a cafe it will be called
# 0xBADDECAF or just 0xCAFE, but what else could it be?
while (<>) {
my @words = split;
foreach my $word (@words) {
next if $word =~ /[^a-fA-F]/;
next if $seen{$word};
$seen{$word} = 1;
}
}
foreach my $word (sort (keys %seen)) {
print $word, " ";
}
tate$ hexword books/bibleKJV.txt books/dictionary96.txt books/warAndPeace.txt
A AB Abbe Abda Add Added B BE Baca Bad Be C D De Dead Ebed F Feed a ab abaca abb
e accede ace ad add added b babe bad bade be bead bed bee beef c cab cd ce cf d
da daff de dead deaf deed e ebb efface f facade face faced fade faded fed fee fe
ed
That’s it? Hmmm. Faded beef cafe? Feed da dead cafe? I guess I should just face da dead beef, I will never own a cafe.
poetry
[tate]$ cat school.pl
while($time)
{
study until $summer;
goto sleep unless $school;
my $time_here = undef and exists $the{exit};
do {
bless $all
, accept $all
, connect $all
, join $all
} until last BREATH;
while(left) {
sin;
bless@ed;
}
}
return ??;
[tate]$ perl -c school.pl
school.pl syntax OK
poetry
[tate]$ cat sleep
try {
sleep;
}
catch {
do { turn; }
until(ready);
}
finally {
awake;
}
[tate]$ ./sleep
bash: ./sleep: Permission denied
[tate]$ perl sleep
Segmentation fault
[tate]$
poetry
Prepare for your entire world to be rocked.
len returns the number of characters in an expression.
datalength returns the number of bytes in an expression.
So this broke my intuition twice recently:
1. len(' ') (single space) returns 0.
To quote from BOL:
Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
So, if the blank character is the only character in the string, is it a trailing blank? Apparently so.
2. nvarchar will return what you want with len in most cases, but datalength for space allocation exercises.
declare @nData nvarchar(max)
select len(''), datalength('')
select len('1 '), datalength('1 ')
select len(' '), datalength(' ')
set @nData = 'A'
select len(@nData), datalength(@nData)
returns the following:
blank blank
----------- -----------
0 0
(1 row(s) affected)
charSpace charSpace
----------- -----------
1 2
(1 row(s) affected)
singleSpace singleSpace
----------- -----------
0 1
(1 row(s) affected)
uSingle uSingle
-------------------- --------------------
1 2
(1 row(s) affected)
sqlserver
If you work with more than one database and have the constant need to run the same query across multiple servers, the undocumented stored procedure sp_MSforeachdb can be useful; see below example comparing stored procedures across servers using a simple checksum to tell if they are the same exact contents. sp_msforeachtable is another oldie but goodie that can be used for do an action per table or examine table properties easily - such as examining table properties that are not exposed from sys.tables.
If you are using SQL Server 2008 there is a more fully supported way to do this and many other nice admin features: Multiple Server Query Execution in SQL Server 2008.
Example of sp_MSforeachdb to test if stored procedure contents are exactly the same across databases:
declare @sql varchar(max), @procname sysname
set @procname = 'cspExampleList'
set @sql =
'
use ?
select db_name(), sp.name, checksum(sc.text)
from sys.procedures sp
join syscomments sc on sc.id = sp.object_id
where name =
'
set @sql = @sql + '''' + @procname + ''''
-- select @sql
create table #Results (dbname varchar(max), procname varchar(max), chksum int)
insert into #Results
exec sp_MSforeachdb @sql
select * from #Results
drop table #Results
sqlserver