|
|
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)...
I created a small table with about 150,000 records in order to do some aggregations. There are some prerequisites to comply for, but that is easily done with replace function.
With Azure, it took almost 4 times as long time as SQL Server Express on my old development machine to complete the query.
With this simple test, I find the use for Azure very limited, as of today. It is excellent for small companies who is not hosting their own environment themself. For companies on a web hosting company which doesn't support Microsoft SQL Server it will do just fine.
But today, almost...
The few last days, our hosting company have updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers.
The problem with this is that one of the databases, Yoda, needed 1.5-2.0 hours to start up due to "In Recovery" status.
I asked around what could be the cause of this and also read some excellent posts by Kimberley Tripp
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
I saw the undocumented DBCC LogInfo command and decided to give it a go. To my surprise, there were 33,636 records returned. As a general rule of thumb, you should have about 8-16 VLF...
Yesterday, I decided to install Microsoft SQL Server 2008 R2 (November CTP) on my local machine. I already had SQL Server Express 2008 installed so the first step was to uninstall Express, since there seemed to be no upgrade path.
Uninstalling went great.
I then installed 2008 R2 and rebooted my machine. I attached my sample databases, and my test database. To my surprise, the Test database was now corrupted!
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred...
This is a piece of code I often use to see if the database server is running in a virtual environment. That is not always obvious or known by the developers.
DECLARE @Result TABLE
(
LogDate DATETIME,
ProcessInfo NVARCHAR(MAX),
Text NVARCHAR(MAX)
)
INSERT @Result
EXEC sys.xp_readerrorlog 0, 1, 'System Manufacturer', 'VMware'
IF EXISTS (SELECT * FROM @Result)
SELECT 'It seems you are running on VMware.' AS Msg
ELSE
SELECT 'It seems you are not running on VMware.' AS Msg
If you test this in your environment and found other virtual manufacturers, please let me know so I can add them in the code above.
Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was
declare @s varchar(100)
set @s = 'xxxx yyyyy zzzzzz'
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '')
I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen.
Run this on your own risk, as you will see soon.
Select q,
len(q)
from (
SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) +...
Yesterday I hade the unfortenate task to change the database collation for a particular database.
Not only the default database collation should be changed, but also all columns!
After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed.
I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future
Why a script? Because most...
I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance.
For the default instance, I have a database named Test on a separate partition of my laptop harddrive.
After installing R2, I tried to attach the Test database, and failed, because I didn't detach the database from the default instance.
So now I detached the Test database from the default instance but didn't make it because it was marked as suspect, even if the icon displayed good. So I took the Test database Offline and then detached it (there...
This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys.
If that's the case, see here how to concatenate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
;WITH Yak
AS (
SELECT ROW_NUMBER() OVER (ORDER BY o.[schema_id]) AS RowID,
QUOTENAME(o.name) AS CONSTRAINT_NAME,
QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS FOREIGN_TABLE_SCHEMA,
QUOTENAME(po.name) AS FOREIGN_TABLE_NAME,
QUOTENAME(rccu.COLUMN_NAME) AS FOREIGN_COLUMN_NAME,
QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS PRIMARY_TABLE_SCHEMA,
QUOTENAME(ro.name) AS PRIMARY_TABLE_NAME,
QUOTENAME(rc.name) AS PRIMARY_COLUMN_NAME,
CASE fk.is_disabled
WHEN 0 THEN 'CHECK'
ELSE 'NOCHECK'
END AS [ENABLED]
FROM sys.foreign_keys AS fk
INNER JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]
INNER JOIN sys.objects AS po ON po.[object_id] = fk.parent_object_id
INNER JOIN sys.objects AS ro ON ro.[object_id]...
Some time ago, I displayed how to work with XML data when searching for data stored in a XML column.
Here Some XML search approaches and here Updated XML search (test case with variables).
Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference.
And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about...
With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement.
The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there.
With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML?
Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query!
In this example I am showing you how to audit certain...
Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.
The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process.
First I am going to show you the ordinary @@ERROR check which most of you are used to.
IF OBJECT_ID('uspTest_2000') IS NOT NULL
...
SELECT @@SERVERNAME AS SqlServerInstance,
db.name AS DatabaseName,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize,
SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,
SUM(af.size / 128.0E) AS TotalSize
FROM master..sysdatabases AS db
INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases
AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW') -- Sample databases
GROUP BY db.name
SELECT db.name AS DatabaseName,
bf.logical_name AS LogicalName,
CASE bs.[type]
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
ELSE 'Unknown'
END AS BackupType,
CASE bf.file_type
WHEN 'D' THEN 'SQL Server data file'
WHEN 'L' THEN 'SQL Server log file'
WHEN 'F' THEN 'Full text catalog'
ELSE 'Unknown'
END AS FileType,
bs.user_name AS UserName,
bs.backup_start_date AS StartDate,
bs.backup_finish_date AS FinishDate,
CAST(bs.software_major_version AS VARCHAR(11)) + '.'
...
I had a strange scenario today and I can't reproduce it.
I changed current database to adventureworks and ran following code
DECLARE @SQL NVARCHAR(200)
SET @SQL = 'SELECT DB_NAME()'
EXEC sp_executesql @SQL
EXEC (@SQL)
The sp_executesql statement returned "master" and exec statement returned "adventureworks".
Anyone knows why?
SELECT cpu_count AS [Logical CPUs],
cpu_count / hyperthread_ratio AS [Physical CPUs]
FROM sys.dm_os_sys_info
Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table.
The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%.
If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has...
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously.
What if you want to wait for the job to finished?
Try this
CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job
(
@jobName SYSNAME
)
AS
SET NOCOUNT ON
DECLARE @jobID UNIQUEIDENTIFIER,
@maxID INT,
@status INT,
@rc INT
IF @jobName IS NULL
BEGIN
RAISERROR('Parameter @jobName have no value.', 16, 1)
RETURN -100
END
SELECT @jobID = job_id
FROM msdb..sysjobs
WHERE name = @jobName
IF @@ERROR <> 0
BEGIN
RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)
RETURN -110
END
IF @jobID IS NULL
BEGIN
RAISERROR('Job %s does not exist.', 16, 1, @jobName)
RETURN -120
END
SELECT @maxID...
I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does.
The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements.
According to Books Online, there is no sure way to guarantee the order of "streams" to execute, but it seems SQL Server favors INSERT / UPDATE / DELETE order.
CREATE TABLE tTemp
(
i INT,
j INT
)
INSERT tTemp
(
i,
j
)
SELECT 1, 0 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT...
/*******************************************************************************
Initialize communication variables
*******************************************************************************/
SET NOCOUNT ON
DECLARE @pathProc VARCHAR(255),
@pathFunc VARCHAR(255),
@pathTrig VARCHAR(255),
@pathView VARCHAR(255),
@cmd NVARCHAR(4000),
@pathBase VARCHAR(256)
SELECT @pathBase = '\\Archive\Documents\Projects\Peso\Code\',
@pathProc = @pathBase + 'Stored Procedures\',
@pathFunc = @pathBase + 'Functions\',
@pathTrig = @pathBase + 'Triggers\',
@pathView = @pathBase + 'Views\'
SET @cmd = 'md "' + @pathProc + '"'
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'md "' + @pathFunc + '"'
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'md "' + @pathTrig + '"'
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'md "' + @pathView + '"'
EXEC master..xp_cmdshell @cmd, no_output
/*******************************************************************************
Stage all existing relevant code
*******************************************************************************/
CREATE TABLE TempDB..CodeOut
(
spID INT,
[uID] INT,
colID INT,
codeText NTEXT,
isProc TINYINT,
isFunc TINYINT,
isTrig...
Today I had to write some code to dynamically get the job name currently running.
DECLARE @SQL NVARCHAR(72),
@jobID UNIQUEIDENTIFIER,
@jobName SYSNAME
SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
SELECT @jobName = name
FROM msdb..sysjobs
WHERE job_id = @jobID
This is a piece of code I use to create a resultset from and display in Outlook calendar.
CREATE PROCEDURE dbo.uspGetScheduleTimes
(
@startDate DATETIME,
@endDate DATETIME
)
AS
/*
This code is blogged here
http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx
*/
SET NOCOUNT ON
-- Create a tally table. If you already have one of your own please use that instead.
CREATE TABLE #tallyNumbers
(
num SMALLINT PRIMARY KEY CLUSTERED
)
DECLARE @index SMALLINT
SET @index = 1
WHILE @index <= 8640
BEGIN
INSERT #tallyNumbers
(
num
)
VALUES (
@index
)
SET @index = @index + 1
END
-- Create a staging table for jobschedules
CREATE TABLE #jobSchedules
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
serverName SYSNAME NOT NULL,
...
This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008.
For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to.
With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...
DECLARE @Interfaces TABLE
(
RowID INT IDENTITY(0, 1),
Interface CHAR(38),
IP VARCHAR(15)
)
INSERT @Interfaces
(
Interface
)
EXEC master..xp_regenumkeys N'HKEY_LOCAL_MACHINE',
N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'
DECLARE @RowID INT,
@IP VARCHAR(15),
@Key NVARCHAR(200)
SELECT @RowID = MAX(RowID)
FROM @Interfaces
WHILE @RowID >= 0
BEGIN
SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface
FROM @Interfaces
WHERE RowID = @RowID
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'DhcpIPAddress',
@IP OUTPUT
IF @IP <> '0.0.0.0'
UPDATE @Interfaces
SET IP = @IP
WHERE RowID = @RowID
SET @RowID = @RowID - 1
END
SELECT IP
FROM @Interfaces
WHERE IP IS NOT NULL
When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS.
Here are some links to remedy the problems
http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx
http://technet.microsoft.com/en-us/network/bb545423.aspx
CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode
(
)
RETURNS INT
AS
BEGIN
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@LoginMode INT
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT
IF @@ERROR <> 0 OR @InstanceName IS NULL
RETURN NULL
SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@LoginMode OUTPUT
RETURN @LoginMode
END
CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode
(
@MixedMode BIT
)
AS
SET NOCOUNT ON
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@NewLoginMode INT,
@OldLoginMode INT
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT
IF @@ERROR <> 0 OR @InstanceName IS NULL
BEGIN
RAISERROR('Could not read SQL Server instance name.', 18, 1)
RETURN -100
END
SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@OldLoginMode OUTPUT
IF @@ERROR <> 0
BEGIN
RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)
RETURN -110
END
IF @MixedMode IS NULL
BEGIN
RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)
RETURN -120
END
IF...
DECLARE @Stage TABLE
(
RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(90),
Section INT
)
INSERT @Stage
(
Data
)
EXEC xp_cmdshell 'ipconfig /all'
DECLARE @Section INT
SET @Section = 0
UPDATE @Stage
SET @Section = Section = CASE
WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1
ELSE @Section
END
SELECT MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,
MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,
MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,
MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END)...
CREATE PARTITION FUNCTION pfOrderDate
(
DATETIME
)
AS RANGE RIGHT FOR VALUES (
'20000101',
'20010101',
'20020101'
)
GO
CREATE PARTITION SCHEME psYak
AS PARTITION pfOrderDate
ALL TO ([PRIMARY])
GO
CREATE TABLE Orders
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL
)
ON psYak(OrderDate)
GO
CREATE CLUSTERED INDEX IX_OrderID
ON Orders
(
OrderID
)
CREATE NONCLUSTERED INDEX IX_OrderDate
ON Orders
(
OrderDate
)
INCLUDE (
OrderID,
CustomerID
)
GO
INSERT Orders
(
OrderID,
CustomerID,
OrderDate
)
SELECT 1, 'Peso', '20011225' UNION ALL
SELECT 2, 'Jennie', '20020314'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderDate = '20011225'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderID = 1
DROP TABLE Orders
DROP PARTITION SCHEME psYak
DROP PARTITION FUNCTION pfOrderDate
For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below.
Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.". So how can the IDENT_CURRENT() return 1 for a newly created table with no inserted records? @@IDENTITY and SCOPE_IDENTITY works as expected.
CREATE TABLE #Temp
(
RowID INT IDENTITY(1, 1),
theValue INT
)
SELECT *
FROM #Temp
SELECT @@IDENTITY,
IDENT_CURRENT('#Temp'),
SCOPE_IDENTITY()
INSERT #Temp
SELECT 99
SELECT *
FROM #Temp
SELECT @@IDENTITY,
IDENT_CURRENT('#Temp'),
SCOPE_IDENTITY()
INSERT #Temp
SELECT 123456
SELECT *
FROM #Temp
SELECT @@IDENTITY,
...
Every now and then I see sites where commands are concatenated and sent to database server.
The author must really trust the user inputs!
For every system built this way, you can expect at least one attack with SQL injection. In some cases you might not be aware of the attack, and sometimes you are aware.
Here is an example of a "friendly" attack, that just promotes a site and when you click the link you execute a javascript who knows do what?
In this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102737
and this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101673
there are examples of SQL injection attacks.
As I wrote in the first topic, "What if the...
SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries.
I find sys.dm_db_index_physical_stats very useful and often write this type of code
SELECT index_id,
page_count
FROM sys.dm_db_index_physical_stats(DB_ID('MyDB'), OBJECT_ID('MyTable'), NULL, NULL, NULL)
to find out if the query optimizer has choosen the "right" index for the query.
This can be done in SQL Server 2000 too!
Use the DBCC SHOWCONTIG command. Maybe most of you have only used this with tables too see table fragmentation?
Well, you can use it for indexes too.
Use
DBCC SHOWCONTIG ('MyDB..MyTable') WITH ALL_INDEXES, TABLERESULTS
http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx
Find it here here
http://support.microsoft.com/default.aspx/kb/949095/
Soon available here
http://support.microsoft.com/default.aspx/kb/949095/
This is an updated version for SQL 2005 and later to search all code for a specific keyword
SELECT p.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM (
SELECT OBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROM SYSOBJECTS AS so
WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHERE p.Body LIKE '%YourKeyWordHere%'
The types are
C = CHECK constraint
D = Default or DEFAULT constraint
FN = Scalar function
IF = In-lined table-function...
I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema
and thought that someone could benefit from this code
exec
sp_MSforeachtable "PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '? modified'; PRINT ''"
I worked with this topic recent weekend and posted the final functions here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454
The general idea is to have a generic purge functionality.
After a good nights sleep when almost all pieces fit together here
weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx
I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint.
It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes.
DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY)
INSERT @Items
SELECT 1 UNION ALL
SELECT 2
SELECT *
FROM @Items
INSERT @Items
SELECT 1
INSERT @Items
SELECT 2
INSERT @Items
SELECT 3
SELECT *
FROM @Items
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET.
But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.
The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.
-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]')
If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls',
...
Let’s play with the new OUTPUT operator!
-- Setup TableA & TableB
CREATE TABLE #TableA
(
i INT
)
CREATE TABLE #TableB
(
i INT
)
CREATE TABLE #TableC
(
iOld INT,
iNew INT
)
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Insert into TableA
INSERT #TableA
OUTPUT inserted.i
INTO #TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Delete from TableA
DELETE a
OUTPUT 10 * deleted.i + 49
INTO #TableB
FROM #TableA AS a
WHERE i = 2
-- Check TableA and TableB
SELECT 'A'...
I was involved in a discussion today about the ISNUMERIC() function
Someone proposed a nested solution like this
SELECT Column_Name
from (
SELECT Column_Name
FROM (
select 'staff' as Column_Name union all
select '234000' as Column_Name union all
select '12d1' as Column_Name union all
select '45e0' as Column_Name union all
select '$123.45' as Column_Name union all
select '$12,345' as Column_Name
) as Table_Name
WHERE ISNUMERIC(Column_Name) = 1
) as d
where CAST(Column_Name AS INT) <= 1000000
But is does not work. I suggested an alternative method that seems to work.
SELECT d.Column_Name
from (
SELECT x.Column_Name
FROM (
select 'staff' as Column_Name union all
select '234000' union all
select '12d1' union...
I came across this solution for some months ago on another forum.
It is very handy, so I thought i should share it.
CREATE TABLE myTable(Document varbinary(max))
INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)
|