Tag Archives: #SQLServer

All about Microsoft SQL Server

Constraint Violation Errors on UDTT

#0364 – SQL Server – Using Primary Key, Check and other Constraints with User Defined Table Types (UDTT)

Leave a reply

As database systems and their interactions and interfaces with systems become more complex, a large amount of complex data is exchanged through the system boundaries. Table Valued parameters are frequently used to exchange data. If the structure of the data being exchanged is known, often systems (which work with  SQL 2008 and above) will use User defined table types (UDTTs).

Depending upon the domain, data being exchanged across system boundaries may need to be constrained to a number of constraints, especially check and unique constraints. My encounter with UDTTs was informal – I learnt about them along the way and never explored them fully. Recently, I was wondering if I could use constraints with UDTTs.

And so, I decided to conduct a small experiment. I created the following UDTT. As can be seen from the definition, I have created the UDTT with the following constraints.

  • Primary Key constraint
  • Unique constraint
  • Default constraint
  • Check constraint
USE tempdb;
GO
CREATE TYPE dbo.UDTTDDLTest AS TABLE
    ( RecordId INT NOT NULL,
      RecordValue VARCHAR(50) NOT NULL UNIQUE,
      RecordStatus TINYINT NOT NULL DEFAULT(2)
      PRIMARY KEY CLUSTERED (RecordId),
      CHECK (RecordStatus>= 1 AND RecordStatus <= 3)
    );
GO

SQL Server allows creation of the constraint without any issues. I then tried to run the following code that attempts to insert various combinations of data into the user defined table type.

USE tempdb;
GO

SET NOCOUNT ON;

DECLARE @myVariable AS dbo.UDTTDDLTest;

--Normal insertion
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (1,
        'SQL Twins',
        1
       );


SELECT * FROM @myVariable;

--Checking effect of default constraints
INSERT INTO @myVariable (RecordId, RecordValue)
VALUES (2,
        'SQL Twins with Default'
       );

SELECT * FROM @myVariable;

--Checking effect of primary key constraint
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (2,
        'Oops...duplicate key',
        1
       );

SELECT * FROM @myVariable;

--Checking effect of check constraint
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (3,
        'Bad Status!',
        4
       );

SELECT * FROM @myVariable;

--Checking effect of unique constraint. The value "SQL Twins" is already in the UDTT,
--so if the constraint is in effect, we will get an error
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (4,
        'SQL Twins',
        1
       );


SELECT * FROM @myVariable;
GO

As can be seen from the output below, SQL Server successfully prevented me from entering data that violated any one of the constraints.

Msg 2627, Level 14, State 1, Line 38
Violation of PRIMARY KEY constraint ‘PK__#B40743E__FBDF78E9F9E4365B’. Cannot insert duplicate key in object ‘dbo.@myVariable’. The duplicate key value is (2).
The statement has been terminated.
Msg 547, Level 16, State 0, Line 47
The INSERT statement conflicted with the CHECK constraint “CK__#B40743ED__Recor__B5EF8C5F”. The conflict occurred in database “tempdb”, table “@myVariable”.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 57
Violation of UNIQUE KEY constraint ‘UQ__#B40743E__4D9E41B64A9AE451’. Cannot insert duplicate key in object ‘dbo.@myVariable’. The duplicate key value is (SQL Twins).
The statement has been terminated.

Records That Inserted Successfully

Constraint Violation Errors on UDTT

Summary

Constraints (Primary Key, Default, Unique and Check constraints) are supported by User Defined Table Types (UDTTs) in Microsoft SQL Server and can be used to ensure the quality of data being exchanged via UDTTs.

Further Reading

  • Passing Table valued parameters to a table valued function – Underappreciated features of Microsoft SQL Server [Link]
  • SQL Server 2000-Alternate to Table Valued Parameters-Share Temporary Tables with a stored procedure [Link]
  • User Defined Table Types [Link]

Until we meet next time,
Be courteous. Drive responsibly.

#0363 – SQL Server – T-SQL Script to generate week date ranges from any given day

Leave a reply

Recently, I was asked to help out in implementing a requirement for a reporting application. The application was designed to report weekly consumption trends from the day the report is run, and we therefore had to generate a lookup with week long date ranges from any given day.

For example, if the report is run from November 30, 2015, the week ends on December 06, 2015 and subsequent weeks are at 7 days intervals from that point on. If the report is run on December 02, 2015, the first week ends on December 08, 2015 and so on.

In this post, I present two scripts that I created as PoC for the implementation.

Script #01

The first script uses a simple approach – it basically runs a WHILE loop starting from the start date supplied to the script and keeps adding 7 days to the date supplied as the start date. This loop continues until the derived start date exceeds the end date supplied for generating the lookup.

USE tempdb;
GO
DECLARE @weekStartDate DATE = '2015-12-02';
DECLARE @weekEndDate DATE = '2016-02-08';
DECLARE @iterationDate DATE = @weekStartDate;
DECLARE @weekNumberIterator INT = 1;

DECLARE @weekDay TABLE (RelativeWeekNumber INT  NOT NULL,
                        WeekStartDate      DATE NOT NULL,
                        WeekEndDate        DATE NOT NULL
                       );

--Generator
WHILE DATEDIFF(DAY,@iterationDate,@weekEndDate) > 0
BEGIN
    INSERT INTO @weekDay (RelativeWeekNumber, WeekStartDate, WeekEndDate)
    SELECT @weekNumberIterator             AS RelativeWeekNumber,
           @iterationDate                  AS WeekStartDate,
           DATEADD(DAY, 6, @iterationDate) AS WeekEndDate;     --Week will end 6 days AFTER start date

    SELECT @iterationDate = DATEADD(DAY, 7, @iterationDate),   --New week will start 7 days AFTER start date
           @weekNumberIterator += 1;                           --The relative week number is a simple iterator
END

SELECT wd.RelativeWeekNumber,
       wd.WeekStartDate,
       wd.WeekEndDate
FROM @weekDay AS wd;
GO
/*****************
**** RESULTS *****
*****************/
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1                  2015-12-02    2015-12-08
2                  2015-12-09    2015-12-15
3                  2015-12-16    2015-12-22
4                  2015-12-23    2015-12-29
5                  2015-12-30    2016-01-05
6                  2016-01-06    2016-01-12
7                  2016-01-13    2016-01-19
8                  2016-01-20    2016-01-26
9                  2016-01-27    2016-02-02
10                 2016-02-03    2016-02-09

(10 row(s) affected)

Script #02

This version uses a recursive CTE to achieve the same result.

-----Alternate Logic, using Recursive CTE
DECLARE @recursiveWeekStartDate DATE = '2015-12-02';
DECLARE @recursiveWeekEndDate DATE = '2016-02-08';

;WITH WeekPatternGenerator (RelativeWeekNumber, WeekStartDate, WeekEndDate)
AS (--Anchor Member
    SELECT 1 AS RelativeWeekNumber,
           @recursiveWeekStartDate AS WeekStartDate,
           DATEADD(DAY, 6, @recursiveWeekStartDate)  AS WeekEndDate
    UNION ALL
    --Recursive Member
    SELECT (wpg.RelativeWeekNumber + 1) AS RelativeWeekNumber,
           DATEADD(DAY,7,wpg.WeekStartDate) AS WeekStartDate,
           DATEADD(DAY,6,DATEADD(DAY,7,wpg.WeekStartDate)) AS WeekEndDate
    FROM WeekPatternGenerator AS wpg
    WHERE wpg.WeekStartDate < DATEADD(DAY,-6,@recursiveWeekEndDate)
  )
SELECT wpgCTE.RelativeWeekNumber,
       wpgCTE.WeekStartDate,
       wpgCTE.WeekEndDate
FROM WeekPatternGenerator AS wpgCTE;
GO
/*****************
**** RESULTS *****
*****************/
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1                  2015-12-02    2015-12-08
2                  2015-12-09    2015-12-15
3                  2015-12-16    2015-12-22
4                  2015-12-23    2015-12-29
5                  2015-12-30    2016-01-05
6                  2016-01-06    2016-01-12
7                  2016-01-13    2016-01-19
8                  2016-01-20    2016-01-26
9                  2016-01-27    2016-02-02
10                 2016-02-03    2016-02-09

(10 row(s) affected)

The Reverse – Requirement to find weekdays between two dates

In order to validate the bigger sets of date ranges, one can use the approaches documented in the post [Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates] by Pinal Dave [B|T].

Until we meet next time,
Be courteous. Drive responsibly.

#0362 – SQL Server – Change Detection in Microsoft SQL Server – Limitations of T-SQL: BINARY_CHECKSUM and CHECKSUM

Leave a reply

Identification of changes made to the data in a system is an important aspect of data storage design and data cleanup/quality improvement activities. For most enterprise systems, the need to implement change detection is driven by some sort of auditing requirements. A couple of years ago, I authored a series of articles on SQLServerCentral.com and on this blog around data change and tamper detection mechanisms available in Microsoft SQL Server. These are:

  • An in-depth look at change detection in SQL Server – Part 01 [Link]
  • An in-depth look at change detection in SQL Server – Part 02 [ Link]
  • HASHBYTES: Is CHECKSUM really required? [Link]
  • HASHBYTES-String or binary data would be truncated: Msg 8152 [ Link]

A recent coincidence at work prompted me to write this post. I was working on comparing a set of records from one table to another after a data cleanup exercise when I realized that a couple of my checksums were coming up as 0, i.e. a blank string (as disccussed in my article on Chage Detection, part 01). The twist to the tale was that there were no blank strings in the sample that I was using.

The Problem

In order to demonstrate the issue clearly, I have prepared the following sample. As can be seen from the sample, both CHEKSUM and BINARY_CHECKSUM work as expected as long as the string under evaluation is less than 26,000 characters in length. As soon as the string hits the 26,000 mark, the functions stop working.

USE tempdb;
GO

DECLARE @stringPatternToReplicate VARCHAR(MAX) = 'a'
DECLARE @stringPatternToEvaluate VARCHAR(MAX)
DECLARE @replicateTillLength INT = 25999
 
SELECT @stringPatternToEvaluate = REPLICATE(@stringPatternToReplicate,@replicateTillLength);
 
SELECT LEN(@stringPatternToEvaluate) AS StringLength,
       CHECKSUM(@stringPatternToEvaluate) AS CheckSumForString,
       BINARY_CHECKSUM(@stringPatternToEvaluate) BinaryCheckSumForString,
       @stringPatternToEvaluate AS StringPatternToEvaluate;

--Repeat after incrementing the @replicateTillLength by 1
SELECT @replicateTillLength += 1
 
SELECT @stringPatternToEvaluate = REPLICATE(@stringPatternToReplicate,@replicateTillLength);
 
SELECT LEN(@stringPatternToEvaluate) AS StringLength,
       CHECKSUM(@stringPatternToEvaluate) AS CheckSumForString,
       BINARY_CHECKSUM(@stringPatternToEvaluate) BinaryCheckSumForString,
       @stringPatternToEvaluate AS StringPatternToEvaluate;
GO

Solution?

The quick solution that I moved ahead with was to perform a direct comparison of the strings involved.

Now, we know that CHECKSUM and BINARY_CHECKSUM will not work if the datatype being evaluated is one of: text/ntext/image/cursor/xml. But, in the example provided above, the strings were the classic – VARCHAR with the MAX keyword to allow storage > 8000 characters.

However, I would like to invite views from you, the kind reader on whether you have faced a similar issue in the past or whether you have any other ideas to resolve this issue.

Summary:

Checksum and BINARY_CHECKSUM can fail to detect a change if:

  • The characters involved are not standard ASCII characters, i.e. have an ASCII value greater than 255
  • The string is a blank string
  • The string is more than 25,999 characters in length (as demonstrated above)

Open Item

I would like to invite views from you, the kind reader on whether you have faced a similar issue in the past or whether you have any other ideas to resolve this issue.

I have written up a Microsoft Connect ticket for this issue to look for an official explanation [MS Connect item #2021430].

Further Reading

  • An in-depth look at change detection in SQL Server – Part 01 [Link]
  • An in-depth look at change detection in SQL Server – Part 02 [ Link]
  • HASHBYTES: Is CHECKSUM really required? [Link]
  • HASHBYTES-String or binary data would be truncated: Msg 8152 [ Link]

Until we meet next time,
Be courteous. Drive responsibly.

#0361 – SQL Server – Script to identify the currently running T-SQL query statement

1 Reply

Many a times we encounter a situation wherein a stored procedure is taking a very long time to execute and the team would like to understand – whether the batch is making any progress or not and what statement is currently being executed by the database engine. I am often asked the question:

How do I know what T-SQL query statement is currently running on my SQL Server instance?

Today, I share a small query that would quickly return the following pieces of information:

  1. T-SQL batch being executed
  2. T-SQL Query statement being executed*
  3. Currently encountered Wait type
  4. Currently encountered Wait duration
  5. T-SQL Command Type
  6. Resources Request Status
  7. Host Name (based on the connection string, as supplied by the calling application)
  8. Program Name (based on the connection string)
  9. Login Name
  10. Login Time
  11. Windows ProcessId for the process executing the query (on the host running the calling application)
  12. Last Wait type encountered by the query

How to find the T-SQL Query statement being executed from a batch?

The perplexing question here is that a batch or a stored procedure may have multiple statements – but, getting to the exact statement that is being executed at a given point needs a bit of manipulation.

Basically, the sys.dm_exec_requests DMV returns us information about each request that is currently being executed by SQL Server . The sys.dm_exec_sql_text() returns the text of the SQL batch that is identified by the specified sql_handle. The statement_start_offset  represents the number of bytes within the SQL batch indicated by the sql_handle at which the currently executing SQL Statement starts. Similarly, the statement_end_offset returns the number of bytes within the SQL batch where the currently executing SQL statement ends. Using these 2 values in a simple SUBSTRING function will return us the exact SQL Statement that is being executed by SQL Sever.

SELECT st.text AS CommandBeingExecuted,
          --Query being executed
          SUBSTRING(text,
                    -- starting value for substring
                    CASE WHEN ISNULL(statement_start_offset, 0) = 0 
                         THEN 1
                         ELSE statement_start_offset/2 + 1 END,
                    -- ending value for substring
                    CASE WHEN ISNULL(NULLIF(statement_end_offset, -1), 0) = 0 
                         THEN LEN(st.text)
                         ELSE statement_end_offset/2 
                    END - CASE WHEN ISNULL(statement_start_offset, 0) = 0 
                               THEN 1
                               ELSE statement_start_offset/2 END + 1
                   ) AS SQLStatementBeingExecuted,
       sr.wait_type AS WaitType,
       sr.wait_time AS WaitDuration,
       sr.command AS SQLCommandType,
       sr.status AS RequestStatus,
       ses.host_name AS HostName,
       ses.program_name AS ProgramName,
       ses.login_name AS LoginName,
       ses.login_time AS LoginTime,
       ses.host_process_id AS WindowsProcessId,
       sr.last_wait_type AS LastWaitType
FROM sys.dm_exec_requests AS sr
INNER JOIN sys.dm_exec_sessions AS ses ON sr.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(sr.plan_handle) AS st;

If you have a similar query that you use, please share it in the comments area below.

References

Until we meet next time,
Be courteous. Drive responsibly.

#0360 – SQL Server – sp_attach_db v/s CREATE DATABASE FOR ATTACH – Number of files limitation; Msg. 8144 (Procedure has too many arguments specified)

1 Reply

This post has been a long time coming. I am an advocate of using newer T-SQL and administrative features if situation and compatibility related business requirements allow. In implementation of this ideology the question always comes up from most of my team members as to why a change is required when the old syntax/methods work just as effectively. I often demonstrate the example provided below to them.

In development environments, we generally move databases across various instances via a simple detach-append process. The traditional approach is to use the system stored procedure sp_attach_db. When CREATE DATABASE was enhanced to include support of attaching a database, one of the obvious differences that came up was the number of database files that it supported for the ATTACH operation.

The sp_attach_db system stored procedure can only attach a maximum of 16 database files.

In this scenario, the newer option of CREATE DATABASE is a blessing for very large databases where we have multiple files spread across multiple file-groups and multiple drives.

Allow me to demonstrate this with an example.

USE master ;
GO
--Safety Check
IF DB_ID('AttachDBTest') IS NOT NULL 
    DROP DATABASE AttachDBTest ;
GO

--Create the test database that has more than 16 files
CREATE DATABASE AttachDBTest ON PRIMARY  
    (NAME = Attach1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach2,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach3,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach4,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach5,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach6,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach7,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach8,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach9,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach10,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    (NAME = Attach11,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat11.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach12,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach13,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach14,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach15,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach16,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach17,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach18,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach19,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach20,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) 
LOG ON 
   (NAME = Attachlog1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
--FOR ATTACH
;
GO

--Create Test Table
USE AttachDBTest;
GO
IF OBJECT_ID('dbo.TestTable','U') IS NULL
BEGIN
    CREATE TABLE dbo.TestTable (RecId INT NOT NULL IDENTITY(1,1),
                                RecValue NVARCHAR(20) DEFAULT 'AttachTest'
                               );

END
GO

--Insert Test Data
USE AttachDBTest;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TestTable','U') IS NOT NULL
BEGIN
    EXEC sp_executesql N'INSERT INTO dbo.TestTable (RecValue) DEFAULT VALUES;';
END
GO 10

--Validate Test Data
USE AttachDBTest;
GO
SELECT * FROM dbo.TestTable;
GO

As seen from the screenshots below, the database creation was successful, and so was the insertion of test data.

image

image 

Next, let us detach the database using the system stored procedure sp_detach_db.

--Detach the database
USE master;
GO
EXEC sp_detach_db @dbname = 'AttachDBTest', @skipchecks = 'true' ;
GO

--Confirm that the database has been detached
USE master;
GO
SELECT DB_ID('AttachDBTest') AS AttachDBTestDBID;
GO

image

Now, let us attach the database again with the sp_attach_db system stored procedure.

--Attach the database
USE master;
GO
EXEC sp_attach_db 
    @dbname = 'AttachDBTest', 
    @filename1 = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    @filename2 = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    @filename3 = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    @filename4 = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    @filename5 = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    @filename6 = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    @filename7 = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    @filename8 = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    @filename9 = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    @filename10 = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    @filename11 = 'C:SQLDatabasesDBFileAttachTestAttachdat11.ndf',
    @filename12 = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    @filename13 = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    @filename14 = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    @filename15 = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    @filename16 = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    @filename17 = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    @filename18 = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    @filename19 = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    @filename20 = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    @filename21 = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf';
GO

Msg 8144, Level 16, State 2, Procedure sp_attach_db, Line 0
Procedure or function sp_attach_db has too many arguments specified.

Now, let us try to attach the database via the CREATE DATABASE statement with the FOR ATTACH option.

--Attach the database using the CREATE DATABASE statement with the FOR ATTACH clause
USE master;
GO
CREATE DATABASE AttachDBTest ON PRIMARY  
    (NAME = Attach1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach2,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat2.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach3,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat3.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach4,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat4.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach5,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat5.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach6,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat6.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach7,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat7.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach8,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat8.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach9,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat9.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach10,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat10.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    (NAME = Attach11,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat11.mdf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach12,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat12.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach13,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat13.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach14,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat14.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach15,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat15.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach16,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat16.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach17,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat17.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach18,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat18.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach19,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat19.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Attach20,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachdat20.ndf',
    SIZE = 10MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) 
LOG ON 
   (NAME = Attachlog1,
    FILENAME = 'C:SQLDatabasesDBFileAttachTestAttachlog1.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
FOR ATTACH
;
GO

--Confirm that the database has been detached
USE master;
GO
SELECT DB_ID('AttachDBTest') AS AttachDBTestDBID;
GO

--Validate Test Data
USE AttachDBTest;
GO
SELECT * FROM dbo.TestTable;
GO

image

This example just demonstrates that when Microsoft recommends a switch from one syntax to another, there has to be some reason behind it and that newer options should be evaluated for replacing older ones.

References

Until we meet next time,
Be courteous. Drive responsibly.