Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Administration

Code Audit - The Beginning

For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden. My part will be the technical details of the forecasting application now when our former DBA has left our company. Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these. One function I stumled across is very simple. All it does is to add a timepart from current execution time to...

posted @ Thursday, July 21, 2011 8:44 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Some datatypes doesn't honor localization

This bug has haunted me for a while, until today when I decided to not accept it anymore. So I filed a bug over at connect.microsoft.com, https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed. Here is a very simple repro of the problem DECLARE  @Sample TABLE          (              a DECIMAL(38, 19),              b FLOAT          ) INSERT   @Sample          (              a,              b          ) VALUES   (1E / 7E, 1E / 7E) SELECT   * FROM     @Sample Here is the actual output.                                       a                      b --------------------------------------- ----------------------                   0.1428571428571428400      0,142857142857143   I think that both columns should have the same decimal separator, don't you? //Peter

posted @ Friday, January 14, 2011 9:09 AM | Feedback (12) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

How to determine if you should use full or differential backup?

Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup. -- Prepare staging table for all DBCC outputs DECLARE @Sample TABLE         (             Col1 VARCHAR(MAX) NOT NULL,             Col2 VARCHAR(MAX) NOT NULL,             Col3 VARCHAR(MAX) NOT NULL,             Col4 VARCHAR(MAX) NOT NULL,             Col5 VARCHAR(MAX)         )   -- Some intermediate variables for controlling loop DECLARE @FileNum BIGINT = 1,         @PageNum BIGINT = 6,         @SQL VARCHAR(100),         @Error INT,         @DatabaseName SYSNAME = 'Yoda'   -- Loop all files to the very end WHILE 1 = 1     BEGIN         BEGIN TRY             -- Build the...

posted @ Thursday, October 21, 2010 4:34 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration ]

Bug in SQL Server Management Studio

When I try to rename a node name such as a table or column in Management Studio, I cannot use DELETE key to remove previous characters. However, I can use BACKSPACE key. Please vote here https://connect.microsoft.com/SQLServer/feedback/details/570758/cannot-use-delete-key-in-ssms-and-object-explorer to fix this little, but annoying, issue. //Peso

posted @ Friday, June 25, 2010 10:13 AM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

SQL Server 2008 Compression

Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity. However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around. So I decided to give compression a go, since we use Enterprise Edition anyway. This is the code I use to compress all tables with PAGE compression. DECLARE @SQL VARCHAR(MAX)   DECLARE curTables CURSOR FOR        ...

posted @ Thursday, June 17, 2010 2:16 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Administration ]

Simple script to get logins and default database names

SELECT     u.name,            l.dbname FROM       sys.sysusers AS u INNER JOIN sys.syslogins AS l ON l.sid = u.sid

posted @ Monday, June 14, 2010 1:27 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Simple script to get referenced table and their column names

-- Setup user supplied parameters DECLARE @WantedTable SYSNAME   SET     @WantedTable = 'Sales.factSalesDetail'   -- Wanted table is "parent table" SELECT      PARSENAME(@WantedTable, 2) AS ParentSchemaName,             PARSENAME(@WantedTable, 1) AS ParentTableName,             cp.Name AS ParentColumnName,             OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName,             OBJECT_NAME(parent_object_id) AS ChildTableName,             cc.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cc ON cc.column_id = fkc.parent_column_id                 AND cc.object_id = fkc.parent_object_id INNER JOIN  sys.columns AS cp ON cp.column_id = fkc.referenced_column_id                 AND cp.object_id = fkc.referenced_object_id WHERE       referenced_object_id = OBJECT_ID(@WantedTable)   -- Wanted table is "child table" SELECT      OBJECT_SCHEMA_NAME(referenced_object_id) AS ParentSchemaName,             OBJECT_NAME(referenced_object_id) AS ParentTableName,             cc.Name AS ParentColumnName,             PARSENAME(@WantedTable, 2) AS ChildSchemaName,             PARSENAME(@WantedTable, 1) AS ChildTableName,             cp.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cp ON cp.column_id =...

posted @ Tuesday, June 08, 2010 4:02 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to get current connection settings

SELECT  name AS Setting,         CASE             WHEN @@OPTIONS & number = number THEN 'ON'             ELSE 'OFF'         END AS Value FROM    master..spt_values WHERE   type = 'SOP'         AND number > 0 Or this SELECT  * FROM    sys.dm_exec_sessions WHERE   session_id = @@SPID Or this SELECT  * FROM    sys.dm_exec_request WHERE   session_id = @@SPID

posted @ Saturday, March 20, 2010 9:53 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Get client IP address

Up until now, I have used convuluted approaches to get the current user client IP-address. This weekend I browsed Books Online for SQL Server 2008 R2 (November CTP) and found this new cool function! SELECT  CONNECTIONPROPERTY('net_transport') AS net_transport,         CONNECTIONPROPERTY('protocol_type') AS protocol_type,         CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,         CONNECTIONPROPERTY('local_net_address') AS local_net_address,         CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,         CONNECTIONPROPERTY('client_net_address') AS client_net_address Or this   SELECT  * FROM    sys.dm_exec_connections WHERE   session_id = @@SPID

posted @ Saturday, March 20, 2010 9:48 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration ]

Is it possible to have multiple sets of key columns in a table?

Filtered indexes is one of my new favorite things with SQL Server 2008. I am currently working on designing a new datawarehouse. There are two restrictions doing this It has to be fed from the old legacy system with both historical data and new data It has to be fed from the new business system with new data When we incorporate the new business system, we are going to do that for one market only. It means the old legacy business system still will produce new data for other markets (together with historical data for all markets)...

posted @ Tuesday, February 16, 2010 2:26 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

Full Administration Archive

Powered by:
Powered By Subtext Powered By ASP.NET