Administration
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...
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
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...
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
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
...
SELECT u.name,
l.dbname
FROM sys.sysusers AS u
INNER JOIN sys.syslogins AS l ON l.sid = u.sid
-- 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 =...
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
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
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)...
Full Administration Archive