File as parameter to PowerShell function

 Quite often I find it usefull to have a file as parameter to a PowerShell function. And most times I want to make sure the file exists when the function is called. In a PowerShell advanced function this can be done quick and short by a validation. More precisely by a ValidationScript attribute to the parameter.

A example on a function with a file as parameter with validation on existance could be something like

function Write-LogFile {
    <#
    .DESCRIPTION
    #>
    [CmdletBinding()]
    [OutputType([void])]
    Param(
        [Parameter(Mandatory=$true, ValueFromPipeLine=$true, HelpMessage='...')]
        [ValidateScript({$_.Exists})]
        [System.IO.FileInfo]$LogFile,

Running a call to the function with a file object that does not exist

[System.IO.FileInfo]$theLogFile = "$PSScriptRoot\log.txt"
Write-LogFile -LogFile $theLogFile

… will generate an error like

PS C:\Scripts> .\FileParameter.ps1
Write-LogFile: C:\Scripts\FileParameter.ps1:26
Line |
  26 |  Write-LogFile -LogFile $theLogFile …
     |                         ~~~~~~~~~~~
     | Cannot validate argument on parameter 'LogFile'. The "$_.Exists" validation script for the argument with value
     | "C:\Scripts\log.txt" did not return a result of True. Determine why
     | the validation script failed, and then try the command again.

There are many other ways to validate if a file exists, but I like to use a FileInfo object as it give the functionality without much custom code.

Stored Procedure Guidelines (T-SQL)

Here are some personal guidelines on writing and using a stored procedure. They are mentioned in a somewhat random order. The list and each recommendation will most likely change over time with new experinces, feedback and other new knowledge.

SET NOCOUNT ON

… to avoid unnecessary activity and communication on row count.

Use schema name

... on used tables. Put procedure in custom schema without data.

Use tables

... not views, other procedures and triggers. And only functions with careful big-O considerations

Use sets

... not cursors or sub-selects as SQL Server is set-based. Cursors are fine when on DB2 or Oracle. Sub-selects are often seen on SAS.

Use functional name

... not sp_ or other Hungarian naming. It doesn´t really matter what type the object is.

Use sp_executesql

... for dynamic SQL. Not EXECUTE. In general you should avoid dynamic SQL. This includes prepared statements.

Use specific columns

... only take data that are required. Network traffic is then reduced to the required amount.

Use order of columns

... as given in the tables. One table a time. Re-arranging the columns could add significant resource consumption on each call.

Use keys

... and indexes with care. Use index on foreign keys. Check for covering index. Index usage might change with change of data in used tables.

Use table variables

... not temporary tables. This will keeps activity in buffer and away from storage,

Be direct

... and avoid temporary cached or staged data. Do not use dirty reads!

Be short

... by short transactions. Avoid costly isolation levels such as Repeatble Read. Also you can save some network traffic on often used stored procedures by short names on the procedure and the parameters.

Use effective parameters

... and avoid parameter sniffing. Focus on parameters where the values change a lot. Convert these parameters to local variables when suitable.

Check values

... before usage. Especially parameter values! Do the value check inside the procedure before usage.

Use effective types

... and avoid custom types. Custom types will add resource consumption on resources. And in some situation increase blockings on metadata.

Use effective operators

... avoid costly operators such as LIKE. Be carefull with COALESCE, string operations and aggregated values. T-SQL is for finding and sorting data. Use other languages for complex calculations and text processing.

Use explicit type cast

... not indirect. And you are still somewhat in control.

Use EXISTS

... not COUNT. As this is more effective with T-SQL.

Use effective error handling

... by testing before exception handling with TRY - CATCH. Consider exception handling as last-line-of-defence after tests and checks. Even when using exception handling this should be kept inseide the procedure. Throwing a exception outside the procedure could be rather costly on the resource consumption.

Use MS_Description

... extended property. This helps development with Visual Studio, see SQLAdmin on Descriptions.

Document

... procerdure, each parameter and output/return value in code, detailed and with examples. Also when you are the one and only developer.

Set security explicit

... on procedure through custom database roles on Active Directory groups. Using default database roles is outside your control.

Use ALTER PROCEDURE

... not DROP - CREATE PROCEDURE. Security and other external configurations will be lost on each change.

Monitor

... usage and performance with DMVs, XEvents etc. See Monitoring Performance of Natively Compiled Stored Procedures.

Call procedure effective

... with CommandType.StoredProcedure and SqlParameter objects on the SqlCommandobject. Use careful crafted connection string with secure authentication (SSPI) like Kerberos.

Reference

Kimberly Tripp, SQLSkills: Building High Performance Stored Procedures.

 

Top 10 stored procedure performance tuning tips in SQL server