System Function RAND() within UDF

Task: the system function RAND() use in the user defined function (UDF)
Solution: the system function RAND() is not possible to be used in the user defined function (see Figure 1 to 3). However, this function is possible to be implemented without problems into the stored procedure (see Figure 4 and 6), or to be used by means of a database object “view” ( see Figure 5 to 8 )

Figure 1: Definition of the Table-valued function

Figure 2: Result of SQL query (of the Table-valued and Scalar-valued function)

Figure 3: Definition of the Scalar-valued function

Figure 4: Definition of the stored procedure

Figure 5: definition of the view

Figure 6: Result of SQL query (of the stored procedure and view)

Figure 7: Definition of SQL query using the view

Note: command GO 5 provides the execution of programming batch 5 times

Figure 8: Result of SQL query using the view

Figure 9: Definition of SQL query with the pre-defined initialisation value

Figure 10: Result of SQL query with the pre-defined initialisation value

Note: when entering the initialisation value, an identical result of random value will always be generated

SysObjects

sys.columns

Task: listing of the name and nullability of individual columns from the given table
Solution:

SELECT [name], [is_nullable]
FROM [sys].[columns]
WHERE [object_id] = OBJECT_ID('[dbo].[Test_SysColumns]')

Figure 1: Definition of the table

Figure 2: Result of SQL query



sys.parameters

Task: listing of the I/O parameters from the given stored procedure
Solution:

SELECT [name], [is_output]
FROM [sys].[parameters]
WHERE [object_id] = OBJECT_ID('[dbo].[Test_SelInputParameters]')

Figure 3: Definition of the stored procedure

Figure 4: Result of SQL query



INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE & INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Task: listing of the name for a primary key (or primary keys) from the given table
Solution: see Figure 6

Figure 5: Definition of the table (see Figure 1)

Figure 6: Definition of SQL query

Figure 7: Result of SQL query



ColumnsDefinition (INFORMATION_SCHEMA.COLUMNS)

Task: listing of the columns name, pre-defined value (suitably adjusted), nullability, data type and detailed information for data type from the given scheme and table. The objective is to provide this information for, e.g. validation logic at the application level of client application
Solution: see Figure 9

Figure 8: Definition of the table

Figure 9: Definition of the stored procedure

Figure 10: Definition of the user defined scalar function

Figure 11: Result of the stored procedure

DAC (Dedicated Administrator Connection) in SQL Server 2008/2005 Express Edition

For the first time, DAC appeared in the SQL Server 2005 version. It enables one user from the sysadmins group (that means, the system administrator) to connect to the running instance of SQL Server Database Engine, especially for the purpose of solving occurred problems on the server (note: DAC connection will disconnect all other users from this server.) It will be used, e.g. in cases when the server does not respond to requirements coming from the client’s side but also in decrypting the content of database objects (i.e. of stored procedures, user-defined functions, views and triggers) which were encrypted using the key word WITH ENCRYPTION (note: the possibilities of content decryption by means of implicit encryption of  database objects will be dealt in some of other contributions on this topic.)

In the Express versions, this feature is implicitly deactivated. The procedure to allow DAC for the Express versions is as follows:

• starting up of “Control Panel\System and Maintenance\Administrative Tools\Services”
• stopping of “SQL Server Express” service
• opening of the dialog window “Properties” on the suspended service
• adding “-T7806” into the text box “Start parameters”
• starting of the “SQL Server Express” service

DAC is available either through the SQLCMD utility (e.g. C:>SQLCMD -E -S.\SQLEXPRESS -A) or by means of SSMS (i.e. SQL Server Management Studio). A requirement is a need to use Query Editor with the ADMIN parameter for the server name (e.g. ADMIN:MP\SQLEXPRESS2008), while it is not possible to use the Object Explorer! The connection may be established direct from the given server only. No network connection to such a server is allowed.

Figure 1: Applet Services (Windows Vista)

Figure 2: SQL Server Properties (SQLS 2008)

Figure 3: Management Studio – failure of an attempt to DAC before allowing it (SQLS 2008)

Figure 4: Management Studio – failure of an attempt to DAC before allowing it (SQLS 2005)

Figure 5: Management Studio – failure of an attempt to DAC after allowing it through Object Explorer (SQLS 2008)

Figure 6: SQLCMD – DAC performance (SQLS 2008)

Figure 7: SQLCMD – execution of a query in the database (SQLS 2008)

Figure 8: SQLCMD – failure of an attempt to DAC after restarting the SQL Server service (SQLS 2008)

Figure 9: Management Studio - DAC connection configuration through Query Editor (SQLS 2008)

Figure 10: Management Studio - execution of a query in the database through Query Editor (SQLS 2008)

Figure 11: Management Studio – a case when DAC is already used by other person (SQLS 2008)

Additional reference:

• Dedicated Administrator Connection – SQL School Video
  by Andy Warren
• Enabling the Dedicated Administrator Connection (DAC) in SQL Server Express
  by Brian Kelley

Select of T-SQL Best Practices

• T-SQL Best Practices by Gregory A. Larsen, June 29, 2009
• T-SQL Best Practices – Part 2 by Gregory A. Larsen, July 31, 2009
• Transact-SQL Formatting Standards (Coding Styles) by Robert Sheldon, August 25, 2009
• ::
• My stored procedure “best practices” checklist by Aaron Bertrand, October 30, 2008
• Pre-Compiled Stored Procedures: Fact or Myth by Scary DBA, September 30, 2009
• Twenty tips to write a good stored procedure by Arup Chakraborty, August 10, 2009
• Writing Faster T-SQL by Jacob Sebastian, May 22, 2007
• ::
• Avoiding cursors with SQL Server 2005 by Tim Chapman, March 05, 2009
• Coding SQL Server triggers for multi-row operations by SQLDenis, November 12, 2009
• Comparing Table Variables with Temporary Tables by Wayne Sheffield, June 10, 2009
• Table Variables Are Only in Memory: Fact or Myth by Scary DBA, October 13, 2009
• The Dynamic Tally or Numbers Table by Lynn Pettis, September 22, 2009
• ::
• Dynamic Search Conditions in T-SQL by Erland Sommarskog, February 14, 2009
• If you really must do dynamic SQL… by Colin A. Mackay, September 21, 2009
• OR Condition Performance on SQL Server Pro
• SQL WHERE Clause Optimization on SQL Server Pro
• T-SQL Best Practices – Don’t Use Scalar Value Functions in Column List or WHERE Clauses by Gregory A. Larsen, October 29, 2009
• TSQL LAB 2 – Writing Conditional WHERE Clauses by Jacob Sebastian, September 25, 2009
• Understanding Scans And Seeks by Preethiviraj Kulasingham, August 21, 2009

Hello world!

This blog is my personal opinion and created to preserve personal notes related to SQL Server.

(Note: the Placko’s blog migration from blogengine.net to WordPress.com.)