Tuesday 29 July 2008

Fast row count on large tables

This statement uses Dynamic Management Views to get the row count of a table. It can be considerably faster when we want to get the row count of a very large table, since the conventional way "select count(*) from table" would require a full table scan.


   1:  SELECT
   2:  Total_Rows= SUM(st.row_count)
   3:  FROM
   4:  sys.dm_db_partition_stats st
   5:  WHERE
   6:  object_name(object_id) = 'table_name_here'

Monday 28 July 2008

Offline Browsing

Interesting open source project for offline browsing: http://www.httrack.com/

It can also be used to build HTTP Caches using archived websites copied by it.

Friday 25 July 2008

Dynamic Bulkinsert directory using server name

This command can be used to dynamically create the path for a file based on the current instance. It assumes each instance server has a directory called BulkInsert which we use to store the csv files we want to import.


   1:  select '\\' + substring(@@servername, 1, charindex('\', @@servername)) + 'BulkInsert\filename.csv'

Tuesday 22 July 2008

svn ignore list for C# / Visual Studio files

This is a list of ignore items I find useful. It includes the Ankh and Resharper addons. Note that it also includes the .dll files, which sometimes might be needed, for example if we don't have the source code or for some other reason we don't want to build the dll. In this case if the ignore is applied recursively, we need to manually allow the dll in the directory of interest.

*.suo
*.user
Ankh.*
Bin
*.exe
*.pdb
*.resharper
*.gpState
_ReSharper.*
*/bin
*/obj
*/Release
*/Debug
*.err
*.log
*.obj
*.bin
*.dll
*.LOG
[tT]emp
[tT]empPE
thumbs.db

Friday 18 July 2008

Database ANSI Padding

char , NOT NULL , ANSI PADDING (ON) : Trailing spaces to length of column created

char, NOT NULL, ANSI PADDING (OFF): Trailing spaces to length of column created

char, NULL, ANSI PADDING (ON): Trailing spaces to length of column created

char, NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created, trailing spaces present are preseverd

varchar , NOT NULL , ANSI PADDING (ON) : Variable storage, no trailing spaces created, yet trailing spaces present are preserved

varchar, NOT NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created

varchar, NULL, ANSI PADDING (ON): Variable storage, no trailing spaces created,yet trailing spaces present are preserved

varchar, NULL, ANSI PADDING (OFF): Variable storage, no trailing spaces created

source: http://channel9.msdn.com/forums/TechOff/190401-Variable-length-fields-and-fixed-length-fields-in-MSSQL/

Friday 4 July 2008

Thursday 3 July 2008

How to take over a database...

--Disconnects people from the database


   1:  alter database dbname
   2:  set single_user with rollback immediate

--Allows only db owners to connect. This step must be run because if you leave it in single user mode then the Redgate GUI cannot connect.
--If you are copying and pasting the Redgate script into the same window as the above script then you do not need to run this step.


   1:  alter database dbname
   2:  set restricted_user