Name resolution, default schema, implicit schema Part II

Last section clarified some background, let us continue with Name Resolution Algorithm for Types and XML Schema Collections [Algorithm] As implied by general syntax for referencing entities, types or XML schema collections cannot be referenced across database. If schema name is unspecified, SQL server uses “sys first” algorithm as outlined below. 1.      First, Look in the “sys”…

0

Name resolution, default schema, implicit schema Part I

This series of blogs describe name resolution behavior of schema objects, types and XML schema collections, and how it interacts with default schema, compile plan implicit schema. The first part will lay some background so that we are at the same page. The following shows the general syntax for referring to an object/type/XML-Schema-collection in SQL Server:               [server.]…

2

Server Side Error Handling – Part 2 (Errors and Error Messages)

Each error in the server has two parts – an error message that describes the error that happened in the server and an error action that determines the effect of the error on the statement, level, batch, transaction, connection and/or server. Server has instances of different combinations of errors and error messages. The major ones…

2

Server Side Error Handling – Part 1 (Migrating from @@error to tsql try-catch)

Tsql try-catch was added to improve server side error handling in sql server 2005. This feature should have been part of early T-Sql. Better late than never. In absence of tsql try-catch, server-side error handling was done using @@error. This had necessitated statement abort as the error policy. Under this policy the execution of a…

0

Host Policy level Permission Sets and their permissions

While there is guidance on how to use the various permission sets – SAFE, EXTERNAL_ACCESS, UNSAFE , a list of the various permissions granted in these three helps clear their definitions. SAFE:Code that has this permission set is only allowed to execute and use context connections. EXTERNAL_ACCESS: Code that has been granted external_access can access system…

0

Multiplication and Division with Numerics

It can be surprising to see certain results when doing numeric arithmetic: declare @num1 numeric(38,10)declare @num2 numeric(38,10)set @num1 = .0000006set @num2 = 1.0select cast( @num1 * @num2 as numeric(38,10)) Yields:             .0000010000 Instead of:      .0000006000 Why? Well, Books Online (see Precision, Scale, and Length) dictates the following rule for numeric arithmetics: Operation Result precision Result scale * e1…

1

Newsequentialid (Histrory/Benefits and Implementation)

            In general, we made significant improvements in SQL Server scalability during Yukon.  One of the areas of improvement is replication scalability.  While doing merge replication testing we found out that scaling was severely affected by high number of I/O operations.  The cause of the problem was that new rows were inserted in random disk…

15