/*
This script relates to the following SQL Server Central Forum topic:
Processing strings ( https://www.sqlservercentral.com/forums/topic/processing-strings )
This script provides several tests for the [ParseCSV] SQLCLR Stored Procedure.
A T-SQL installation script (no external DLL) containing only two Stored Procedures is located at:
https://pastebin.com/aqsWiX1e
The source code for the [ParseCSV] and [GarbageCollect] SQLCLR Stored Procedures is located at:
https://pastebin.com/BY8F994R
Date: 2016-04-11
Version: 1.0.0
For more functions like this, please visit: https://SQLsharp.com
Stairway to SQLCLR series: https://www.sqlservercentral.com/stairways/stairway-to-sqlclr
Copyright (c) 2016 Sql Quantum Leap. All rights reserved.
https://SqlQuantumLeap.com
*/
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET NOCOUNT ON;
GO
USE [CSVParser];
GO
------------------------ BEGIN TEST #1 ------------------------------------
-- Simple functional test to make sure that various scenarios are properly handled:
-- 1) text-qualfied fields
-- 2) not all fields need to be text-qualified
-- 3) embedded text-qualifier
-- 4) embedded field delimiter
-- 5) embedded row delimiter
DECLARE @CSV NVARCHAR(MAX) = N'12,12,1231231,fgd,231
g,h,j,"w
ggg",y
"sdf,hhh",32,"dfg"",z""ghd",55,,5564
45
5,6,77
a,';
EXEC dbo.ParseCSV ',', @CSV;
------------------------ END TEST #1 ------------------------------------
------------------------ BEGIN TEST #2 ------------------------------------
USE [CSVParser];
-- FIRST, create the destinaton table.
-- Please note that all fields are NVARCHAR(MAX) because all result set
-- fields coming back from [ParseCSV] are NVARCHAR(MAX).
-- DROP TABLE [dbo].[Destination];
IF (OBJECT_ID(N'dbo.Destination') IS NULL)
BEGIN
PRINT 'Creating table: Destination...';
CREATE TABLE [dbo].[Destination](
[object_id] NVARCHAR(MAX) NOT NULL,
[definition] NVARCHAR(MAX) NULL,
[uses_ansi_nulls] NVARCHAR(MAX) NULL,
[uses_quoted_identifier] NVARCHAR(MAX) NULL,
[is_schema_bound] NVARCHAR(MAX) NULL,
[uses_database_collation] NVARCHAR(MAX) NULL,
[is_recompiled] NVARCHAR(MAX) NULL,
[null_on_null_input] NVARCHAR(MAX) NULL,
[execute_as_principal_id] NVARCHAR(MAX) NULL,
[uses_native_compilation] NVARCHAR(MAX) NULL,
[name] NVARCHAR(MAX) NOT NULL,
[principal_id] NVARCHAR(MAX) NULL,
[schema_id] NVARCHAR(MAX) NOT NULL,
[parent_object_id] NVARCHAR(MAX) NOT NULL,
[type] NVARCHAR(MAX) NOT NULL,
[type_desc] NVARCHAR(MAX) NULL,
[create_date] NVARCHAR(MAX) NOT NULL,
[modify_date] NVARCHAR(MAX) NOT NULL,
[is_ms_shipped] NVARCHAR(MAX) NULL,
[is_published] NVARCHAR(MAX) NULL,
[is_schema_published] NVARCHAR(MAX) NULL,
[extra] VARCHAR(MAX) NULL
) ON [UserData] TEXTIMAGE_ON [UserData];
END;
-- SECOND, generate the test CSV file. The following query should produce about
-- 80 MB of result data (there is slight variation due to the last column being
-- a random number of repetitions per row of a GUID, and not all systems have
-- the same number of entries in the two tables used in the following query.
--
-- On my install of SQL Server 2012, SP2 the following query generated 185,288
-- rows. I adjust the "207" value in the REPLACE on the top SELECT line to get
-- the output to be almost exactly 80 MB. Without the REPLACE function, the same
-- number of rows produced a file that was 674 MB. I would suggest not going over
-- 100 MB for the file since the test uses the INSERT...EXEC construct which
-- stores the results in memory until the EXEC call finishes, before it starts
-- inserting those results into the table.
--
-- To generate the file:
-- 1) Run the query using Results to Grid (the default setting unless you changed it).
-- 2) Right-click in the grid and select "Save Results As...".
-- 3) "Save as type" should be set to "CSV (Comma delimited) (*.csv)"
-- 4) Choose a path and enter in a name (path should be easily accessible; "C:\" is
-- usually restricted; I create a "C:\TEMP" folder).
-- 5) Click the "Save" button.
-- 6) In File Explorer, go to the path you just saved the file in, right-click on the
-- file, and select "Properties" (bottom option).
-- 7) Make a note of the "Size" value, not the "Size on disk" value.
SELECT sasm.[object_id], '"' + REPLACE(LEFT(sasm.[definition], 207), N'"', N'""') + '"' AS [definition],
sasm.[uses_ansi_nulls], sasm.[uses_quoted_identifier], sasm.[is_schema_bound],
sasm.[uses_database_collation], sasm.[is_recompiled], sasm.[null_on_null_input],
sasm.[execute_as_principal_id], CONVERT(BIT, 0) AS [uses_native_compilation],
sao.[name], sao.[principal_id], sao.[schema_id], sao.[parent_object_id], sao.[type], sao.[type_desc],
sao.[create_date], sao.[modify_date], sao.[is_ms_shipped], sao.[is_published], sao.[is_schema_published],
'"' + REPLICATE(NEWID(), (CRYPT_GEN_RANDOM(1) % 5) + 1) + '"' AS [extra]
FROM msdb.[sys].[all_sql_modules] sasm
INNER JOIN msdb.[sys].[all_objects] sao
ON sao.[object_id] = sasm.[object_id]
CROSS JOIN msdb.sys.views;
-- 185,288 rows / 80.0 MB (83,936,807 bytes)
-- THIRD, run the SQLCLR stored proc once with simple input to
-- ensure that the App Domain has been created and that the Assembly
-- has been loaded. This initialization sometimes takes a second or
-- two and we don't want that time throwing off the test results.
EXEC dbo.ParseCSV @Delimiter = N',', @InputString = N'1,"2a,2b,2c",3';
-- FOURTH, make sure that the @FilePath parameter has the correct path
-- and file name for the CSV file that you created in the second step.
-- TRUNCATE TABLE dbo.Destination;
INSERT INTO dbo.Destination ([object_id], [definition], uses_ansi_nulls, uses_quoted_identifier,
is_schema_bound, uses_database_collation, is_recompiled, null_on_null_input,
execute_as_principal_id, uses_native_compilation, name, principal_id,
[schema_id], parent_object_id, [type], type_desc, create_date, modify_date,
is_ms_shipped, is_published, is_schema_published, extra)
EXEC dbo.ParseCSV
@Delimiter = N',',
@InputString = NULL, -- default value not allowed so specify empty or NULL
@FilePath = N'C:\TEMP\TestImportData.csv'; -- replace this value with your path + filename
-- FIFTH, check to make sure that all of the data got loaded. NULL values will import as the word
-- NULL instead of being an actual NULL, but this is just a simple test and that inaccuracy does
-- not affect the timing.
SELECT * FROM dbo.Destination;
-- SIXTH, run again, just to not rely on a single timing. Before we can re-run, however, we
-- should clear out the Destination table:
CHECKPOINT;
TRUNCATE TABLE dbo.Destination;
CHECKPOINT;
-- now go back and repeat the THIRD through FIFTH steps
-- EXEC dbo.GarbageCollect;
-- DBCC DROPCLEANBUFFERS
-- DBCC FREESYSTEMCACHE('ALL')
-- CHECKPOINT;
------------------------ END TEST #2 ------------------------------------
Comments