PROCEDURES AND FUNCTIONS IN .NET LANGUAGES name, into

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES name, into appropriate database tables. You always use sp_adduser to add a user to the database, and do not need to know the details of what happens inside the database. In fact, Microsoft could completely change how SQL Server maintains users in a database, and it would not affect the way you add users to a database. Prior to SQL Server 2005, except for T-SQL the only ways to extend SQL Server were to write an extended stored procedure or a COM component. T-SQL required you to know the T-SQL language. For many this meant learning an alternate programming language used much less than their primary one. For a VB.NET programmer, this might mean stumbling through something like Dim id. Whoops, no, Declare id. Whoops, no, Declare @id as new int. Whoops, no, Declare @id int. Similar relearn-bysyntax- error journeys await programmers from other languages whenever they attempt to write a T-SQL-based stored procedure too. Extended stored procedures require a rather tedious DLL to be created. However C++ programmers can use a wizard in Visual Studio to create this DLL and just fill in the functionality they choose. Likewise, VB6 programmers can create a COM component and add it in SQL Server using the sp_OACreatestored procedure. This allows C++ or VB6 programmers to use a familiar programming environment to extend SQL code. Extended stored procedures and COM components are also more flexible than T-SQL because they can access system services that are outside SQL Server. For example, the extension to SQL Server that allows it to send e-mail is an extended stored procedure. It could not have been written in T-SQL. Extended stored procedures have their own issues. Although it is possible to write extended stored procedures that are secure and reliable, the languages used to create them make this very difficult to do. In general, an extended stored procedure or a COM component must stand a much higher level of scrutiny than a T-SQL-based stored procedure and in some cases cannot match its performance. SQL Server 2005 changes all this. Any .NET language can extend SQL Server. .NET languages run in the Common Language Runtime, the CLR. Extensions running in the CLR can be as safe and reliable as T-SQL and as flexible as an extended stored procedure or a COM component. This means that developers can use a familiar development environment to extend the functionality of SQL Server. In addition, there are some tasks for which the CLR is just better suited. Typically, the CLR is a better choice for operations that involve numeric computations or string manipulation.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

3 Procedures and Functions in .NET Languages T

3 Procedures and Functions in .NET Languages T T -SQL IS A SQL SERVER SPECIFIC LANGUAGE used to extend the functionality of SQL Server. Any language capable of creating a DLL or a COM component can be used to extend the functionality of SQL Server; but extended stored procedures and COM components lack the reliability, security, and, in many cases, the performance that T-SQL provides. With SQL Server 2005, any .NET language can be used to extend the functionality of SQL Server with the same level of reliability, security, and performance that T-SQL provides. Extending SQL Server SQL Server s functionality can be extended by using T-SQL. It encapsulates common tasks and makes it easier to maintain and perform them. It, in effect, allows you to write SQL batches that perform common tasks and store those batches for later reuse directly in SQL Server. Much of the functionality associated with SQL Server does not come from the SQL programming language; it comes from extensions that Microsoft has added to SQL Server using T-SQL, its proprietary language for writing imperative code. For example, there is a Microsoft-written stored procedure to add a new user to a database called sp_adduser. This stored procedure inserts the parameters you pass in, login name and user

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST Some of the ancillary concepts are analogous with respect to deploying the items that an assembly or jarfile contains. The specification includes a bit to indicate whether a deployment descriptor included in the jar file should be read; an analogous concept would be the list of publicly declared methods and types in the CREATE ASSEMBLY or ALTER ASSEMBLY statement. Visibility of classes and methods is defined by using the public or private keywords, as it is in SQL Server 2005, but no notion of visibility at the jar level exists in the SQL specification to correspond to the SQL Server 2005 notion of assembly visibility. References are defined in the ANSI specification only with respect to path names; there is no concept of keeping references in the schema. The ANSI specification contains no notion of code access security level and execution context. Database permissions for managed code within an assembly are part of SQL Server 2005 and, to an extent, the code within a SQL/JRT file as well. We ll discuss this in Chapter 6 on security. Conclusions If your assembly is SAFE or EXTERNAL_ACCESS, nothing you can do will affect SQL security, reliability, or performance. You ll have to work hard to make your app itself unreliable and nonscalable at these safety levels. SAFE or EXTERNAL_ACCESS levels enforce the general principles of no-sharedstate and no multithreaded programming. Writing secure, reliable, and performant code that doesn t follow these tenets is harder and is only possible for assemblies cataloged as UNSAFE. Where Are We? We ve seen how SQL Server 2005 is a full-fledged runtime host and can run your managed code. SQL Server s choices as a runtime host are geared toward security and reliability, with an eye toward performance. We ve seen how to define an assembly (managed DLL) to SQL Server for storage inside the server and how to specify the execution context (identity) and degree of safety with which the code runs. The CLR s Framework class libraries are even categorized by degree of safety; this should ameliorate some of the DBA s concerns. In the next chapter, we ll look at some of the specific code you d write as managed stored procedures, user-defined functions, and triggers and see how this code is defined to SQL Server so that it s indistinguishable from T-SQL procedures to the caller.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

SPECIFICATION COMPLIANCE ALTER ASSEMBLY SomeTypes ADD FILE FROM

SPECIFICATION COMPLIANCE ALTER ASSEMBLY SomeTypes ADD FILE FROM \zmv43typesSomeTypes.pdb go The SQL Server project in Visual Studio 2005 will perform this step automatically when you use the Deploy menu entry. Specification Compliance The ANSI SQL specification attempts to standardize all issues related to relational databases, not just the SQL language. Although some databases allow external code to run inside the database (SQL Server permits this through extended stored procedures), this was not part of the SQL specification until recently. Two specifications appeared at the end of the SQL:1999 standardization process that relate to some of the concepts we re going to be covering in the SQL Server .NET integration portion of this book. These specifications are related to the concept of managed code running inside the database and the interoperation of managed types and unmanaged types, and are called SQL/J part 1 and SQL/J part 2. There are two interesting features of these specs. They were added as addenda to SQL:1999 after the specification was submitted. SQL/J part 1 became SQL:1999 part 12, and SQL/J part 2 became SQL:1999 part 13. This specification was consolidated as part 13 of SQL:200x, and is there known as SQL-Part 13: Java Routines and Types (SQL/JRT). Although the specs are part of the ANSI SQL specification, the only managed language they address is Java, which is itself not a standard. Java was withdrawn from the ECMA standardization process. On the other hand, the .NET CLR is an ECMA standard. Nevertheless it is interesting to at least draw parallels. The closest equivalents to SQL Server 2005 s CREATE ASSEMBLY/ALTER ASSEMBLY/DROPASSEMBLYare SQL/J s SQLJ.INSTALL_JAR,SQLJ.REMOVE_ JAR, SQLJ.REPLACE_JAR procedures. These procedures are not implemented by all the implementers of the standard. Because the SQL/J standard does not state that the actual code needs to be stored in the database, you can also change the defined path where the jar file resides with SQLJ.ALTER_JAR_PATH. Because SQL Server assemblies are stored inside the database, an equivalent function would be unnecessary.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST Your new code can differ from the original only by the fourth part of the version number. Of course, this code must be built against the same version of the runtime and the base class libraries as the original. If there are references to the assembly, any of the method signatures of the methods defined as stored procedures, triggers, and user-defined functions are not allowed to change. If your new version contains user-defined types, ALTER ASSEMBLY not only checks all the methods signatures, but if the serialization format is Native, all the data members must be the same so that the user-defined type instances that have been persisted (as column values in tables) will still be usable. Other types of persisted data will be checked too, if your assembly contains the following: Persisted computed columns that reference assembly methods Indexes on computed columns that reference assembly methods Columns with check constraints that reference assembly user- defined functions (UDFs) You can bypass checking persisted data by specifying the UNCHECKED DATAoption; you must be DBOor db_ddlownerto use this option. The metadata will indicate that the data is unchecked until you use the command DBCC CHECKTABLEto manually check the data in each table. You must be sure that the formulas (content) of user-defined functions have not changed before you use the UNCHECKEDDATAoption, or else corrupted data could result. When you use ALTER ASSEMBLY, there may be user sessions using the old version of the assembly. In order not to disrupt those sessions, SQL Server will create a new AppDomain to load your new code. Existing sessions continue to use the old code in the original AppDomain until logoff; new sessions will be routed to the new AppDomain. When all the existing sessions finish, the old AppDomain is shut down. This is the only (transient) case where two different versions of your code can be running at the same time, but is not the same as side-by-side execution. One common usage for programmers will probably be to add debugging information to use during development. A .NET assembly can be debugged inside SQL Server with any of the usual .NET debuggers for example, Visual Studio 2005. You may have noticed that one of the metadata tables (sys.assembly_files) will contain an additional entry if you have added the .NET debugging symbols for the assembly. This file usually has a .pdbextension when it is produced by the compiler. To add a debug symbols file for the assembly that we just defined earlier, you can execute the following statement.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

MAINTAINING USER ASSEMBLIES earlier needs to be present

MAINTAINING USER ASSEMBLIES earlier needs to be present in SQL Server, but it need not be visible (for example, it is accessed only by other assemblies and not from the outside ). To prevent routines in this assembly from inadvertently being declared publicly via DDL, we can alter its visibility like this. ALTER ASSEMBLY SomeTypes VISIBILITY=OFF To reload the SomeTypes assembly, as long as it is not currently being referenced, we can use the following syntax. ALTER ASSEMBLY SomeTypes FROM \zmv43typesSomeTypes.dll The complete syntax for ALTER ASSEMBLY follows. We ll discuss some of the more esoteric options in Chapters 4 and 5, and the security options in Chapter 6. ALTER ASSEMBLY assembly_name [ FROM { < client_assembly_specifier > | < assembly_bits > [ ,...n ] ] [ WITH < assembly_option > [ ,...n ] ] [ DROP FILE { file_name [ ,...n ] | ALL } ] [ ADD FILE FROM { client_file_specifier [ AS file_name ] | file_bits AS file_name } [,...n ] < client_assembly_specifier > :: = \computer_nameshare-name[path]manifest_file_name < assembly_bits > :: = { varbinary_literal | varbinary_expression } < assembly_option > :: = [ PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE } ] [ VISIBILITY { ON | OFF } ] UNCHECKED DATA In order to change the permission set with ALTER ASSEMBLY, you must have analogous permissions as with CREATE ASSEMBLY that is, you need EXTERNAL_ACCESS privilege to alter an assembly to the EXTERNAL_ACCESS safety level, and you must be sysadmin to alter an assembly to UNSAFE. You also must have analogous file access permissions as with CREATE ASSEMBLYif you are going to load any new code from a file. You can use ALTER ASSEMBLY not only to change individual metadata properties, but to version or reload the code as well. Reloading the code (by using the FROM clause) not only reloads, but revalidates code as well.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST Table 2-4: Contents of sys.assembly_files Column Name Data Type Description assembly_id int ID of assembly to which this file belongs name nvarchar (260) Name of assembly file file_id int ID of file, unique within an assembly content image Bytes of assembly or debug symbols invoked internally only. If you specify IsVisible=true (the default) this means that methods and types in this assembly can be declared as SQL Server methods and types, either through the list properties or directly through DDL. Maintaining User Assemblies (ALTER ASSEMBLY, DROP ASSEMBLY) Although we stated earlier in this chapter that SQL Server loads only a single version of the .NET runtime and base class libraries, assemblies may be versioned, their code reloaded, and properties (including the set of dependent assemblies) altered via DDL. Assemblies may also be dropped via DDL, subject to whether or not they are currently used by other system objects. An example of dropping the SomeTypesassembly defined earlier follows. DROP ASSEMBLY SomeTypes The complete syntax for dropping a defined assembly follows. DROP ASSEMBLY assembly_name [WITH drop_option [,..]] drop_option::= CASCADE | DEPENDENT ASSEMBLIES Note that unless the CASCADE option is specified, the DROP ASSEMBLY statement will fail if any existing routines, user-defined types, or user- defined aggregates exist that refer to the assembly. We ll get back to dependent assemblies in a moment. You may change the properties of an assembly or even modify the assembly code in place using the ALTERASSEMBLYstatement. For example, let s assume that we d decided that the SomeTypes assembly mentioned

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

ASSEMBLIES AND SQL SCHEMAS Table 2-2: Contents of

ASSEMBLIES AND SQL SCHEMAS Table 2-2: Contents of sys.assemblies (continued ) Column Name Data Type Description is_visible bit 1 if the assembly is visible to register T-SQL entry points (functions/procs/triggers/ types/aggregates); 0 if it is intended only for managed callers (that is, provides internal implementation for other assemblies in the database). create_date datetime Date assembly was created or registered. The assembly dependencies are stored in sys.assembly_references, one row per assembly-reference pair. Table 2-3 shows the columns in sys.assembly_references. Note that this table does not contain information about which base class libraries an assembly references. Finally, the assembly code itself is cataloged in sys.assembly_files. In all cases, this table contains the actual code rather than the name of the file where the code resided when it was cataloged. The original file location is not even kept as metadata. In addition, if you have added a debugger file, using the ALTER ASSEMBLY ADD FILE DDL statement, the debug information will appear as an additional entry in the sys.assembly_files table. Table 2-4 shows the contents of the sys.assembly_filestable. We ll discuss more about declaring routines and types in the CREATE ASSEMBLY DDL statement in Chapters 3 and 4. Notice that you can define an assembly that is invisible with respect to defining routines and types to the runtime. Lack of visibility is the default when SQL Server loads dependent assemblies of an assembly defined using CREATE ASSEMBLY. You might do this, for example, to define a set of utility routines to be Table 2-3: Contents of sys.assembly_references Column Name Data Type Description assembly_id int ID of assembly to which this reference belongs referenced_assembly_id int ID of assembly being referenced

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME

HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST Assembly Metadata Information about assemblies and the assembly code itself is stored in three metadata tables. These tables exist on per database, since assemblies are scoped to the database and schema. Sys.assemblies stores information about the assembly itself as well as schema_id, assembly_id, and the .NET version number. The complete list of columns in sys.assembliesis shown in Table 2-2. Table 2-2: Contents of sys.assemblies Column Name Data Type Description name sysname Name of assembly, unique within schema. principal_id int ID of the principal that owns this schema. assembly_id int Assembly identification number, unique within a database. clr_name nvarchar(4000) Canonical string that encodes the simple name, version number, culture, and public key of the assembly. It uniquely identifies the assembly on the CLR side. permission_set tinyint Permission set/security level for assembly, one of: 1 = Safe Access only 2 = External Access allowed 3 = Unsafe Access allowed permission_set_desc nvarchar(60) Description of permission set/security level for assembly, one of: SAFE_ACCESS EXTERNAL_ACCESS UNSAFE_ACCESS

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

ASSEMBLIES AND SQL SCHEMAS have been overhauled, revising

ASSEMBLIES AND SQL SCHEMAS have been overhauled, revising the layout of the metadata information and adding metadata for new database objects. In addition, programmers and DBAs can no longer write to the system metadata. It is really a read- only view. The SQL INFORMATION_SCHEMA, on the other hand, is a series of metadata views defined by the ANSI SQL specification as a standard way to expose metadata. The views evolve with the ANSI SQL specification; SQL:1999 standard INFORMATION_SCHEMA views are a superset of the SQL-92 views. SQL Server 2000 supports the INFORMATION_SCHEMA views at the SQL-92 standard level; some of the SQL:1999 views may be added in SQL Server 2005. SQL Server is, so far, the only major database to support the INFORMATION_SCHEMA views. Listing 2-1 shows code that can be used to retrieve the list of tables in a specific database. The sample uses the system metadata tables, followed by analogous code using the INFORMATION_SCHEMA views. Note that neither query (in SQL Server, the SQL:1999 spec seems to indicate otherwise) includes the system tables in the list of tables. Listing 2-1: Getting Metadata from SQL Server this uses the system metadata tables SELECT * FROM sysobjects WHERE type = U this uses the INFORMATION_SCHEMA SELECT * FROM INFORMATION_SCHEMA.TABLE WHERE TABLE_TYPE = BASE TABLE SQL Server 2005 includes a reorganization of the system metadata tables. This includes renaming the tables to use an arbitrary schema (named SYS) as well as table renames and reorganization of some of the information. The goal, once again, is speed and naming consistency. The equivalent query to the previous two using the new system metadata tables would be as follows: SELECT * FROM SYS.TABLES Note that the information returned by all three queries differs both in the number of columns returned, the column names used, and the information in the resultset. Although a complete description of all the new metadata tables is beyond the scope of this book, it helps to introduce the metadata information that is stored when CREATE ASSEMBLY is executed and to explain assembly properties.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services