Function
-
I'm trying to test a SQL move utitlity that we bought last week due to some slowness issues
I need to create some sort of function with a table variable that cycles through about 10,000 records.
I then need to run it with Sysadmin rights then read/write permission only and test the speed.
Need help construction this function (not fancy just something that will cycle 10,000 rows to test for speed..
-WM
-
Okay, so what exactly do you need help with?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I need help with the syntax of the function
-
Is there something specific that you need help with? I've included the syntax for functions below.
The syntax of a function from Books Online:
Syntax
Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name
(
[ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name
(
[ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name
(
[ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE < table_type_definition >
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
CLR Functions
CREATE FUNCTION [ schema_name. ] function_name
(
{ @parameter_name [AS] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
[ WITH <clr_function_option> [ ,...n ] ]
[ AS ] EXTERNAL NAME <method_specifier>
[ ; ]
Method Specifier
<method_specifier>::=
assembly_name.class_name
.method_name
Function Options
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
<clr_function_option>::=
}
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
Table Type Definitions
<table_type_definition>:: =
(
{ <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [ ,...n ]
)
<clr_table_type_definition>::=
(
{ column_name data_type } [ ,...n ] )
<column_definition>::=
{
{ column_name data_type }
[ [ DEFAULT constant_expression ]
[ COLLATE collation_name ] | [ ROWGUIDCOL ]
]
| [ IDENTITY [ (seed , increment ) ] ]
[ <column_constraint> [ ...n ] ]
}
<column_constraint>::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<computed_column_definition>::=
column_name
AS computed_column_expression
<table_constraint>::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<index_option>::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS ={ ON | OFF }
}
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
This is what I tried to use but I'm still getting and error
I'm sure it's something simple but I can't seem to locate it
Use
ExtractStage1
go
CREATE
FUNCTION avantservetest (@deadline int) RETURNS @table TABLE (EmployeeSSN int null, Company nvarchar null, Employee nvarchar null,)
AS
BEGIN
INSERT
@table
SELECT
EmployeeSSN, Company, Employee
FROM
CW001_Extract_BN531_Health_BAK
WHERE
EmployeeSSN > @deadline
RETURN
END
-
What is the error? Please be more forthcoming with information. You'll get more help if we don't have to drag it out of you.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
You might want to put this in a view or procedure... cause I rarely saw a huge advantage or using a function like this (only assuming what you want to do with that code)... Care to explain what involved in that process?
CREATE FUNCTION dbo.avantservetest (@deadline int)
RETURNS TABLE
AS
RETURN (
SELECT EmployeeSSN, Company, Employee
FROM dbo.CW001_Extract_BN531_Health_BAK
WHERE EmployeeSSN > @deadline
 
-
sorry about the lack of info
I just got it to work. The only reason I was using this was to test the query time depending on the security permission. We have this SQL move utility (JUNK) that uses a function with some sort of a table variable. It executes fine if you have sysadmin rights. I'm trying to determine the least abount of permission that will give them the same performance and the lowest permision. Sick of getting the calls about slow performance...
From testing I think they just need DDL_Admin rights.
Thanks again for you responses
-WM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply