Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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 ------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement