Tag: sqlserverrider

GeoSpatial – GIS File Formats


GIS file formats are the standards that are used to store the Geo-spatial data. These data formats are created by government entities, educational institution, geological information storage and service organization and etc.

There are many popular open source or proprietary formats available. Following are the most popular format which we are using in day today geography data.

  • Raster
  • Vector
  • Grid
  • Others

Raster

Raster data type consists of rows and columns of cells and each cell storing a single value. It uses color code RBG.  An external attribute table can be maintained each cell by rows. Raster data can stored in various formats such file-based structure of TIF, JPEG and etc. We can also use RDBMS to store this data as BLOB.

Vector

GIS data in vector format contains typical geometry shapes such as points, line, polygon and etc. to represents geological location or regions in the earth. Usually, vector data may also comes with the database that describes the attributes of the shapes. For instance, if we use point on a building the attribute database may have its address, phone number and etc. to provide additional details about that location.

To understand better about raster and vector, it is ideal to have a look at the below given image.

raster-vector-gis-i4Vector GIS has more flexible and simplicity to integrate in to the application. It is more developer friendly and simple to storage and use.

An Esri grid is a raster GIS file format used to represent elevation and it is developed by Esri.

There are two formats available now:

  1. A proprietary binary format
  2. A non-proprietary ASCII format

There are different organization and their software uses different formats to store GIS data. Below given list is taken from Wikipedia.

Raster:

Vector:

Other GIS data storage formats

Vector format, WKT and WKB are the useful GIS data storage and manipulation formats in SQL Server. We will learn about that in the next blog post.

SQL SERVER – Fetch the SQL error number using ERROR_NUMBER function


ERROR_NUMBER() 

This function is used to fetch the SQL error number when the SQL Server throws during query execution. It gives the same error number as @@ERROR does but ERROR_NUMBER() function works within the try..catch scope. During the execution we can get different error number in each try…block because of scope boundaries. But, @ERROR gives us the last generated error number though out the program.

It returns an integer value and if this function is called outside the try…catch block then it returns NULL.

Syntax

ERROR_NUMBER()

Example

1. This simple example is to print the error number and the similar message from the system view sys.messages.

SQL Statement

BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

SELECT * FROM sys.messages WHERE message_id = 8134 AND language_id = 1033
GO

Output

pic0

SQL SERVER – Obtain ERROR_STATE code for troubleshooting


ERROR_STATE:

This function returns the error state number of an error occurred inside the try..catch block. This state number is different from @@ERROR number. Error State will differ for a same error but in different situation. So, to pin point the particular cause, ERROR_STATE() is very useful when referring Microsoft KB article for troubleshooting.

it returns an integer value. Error_State must be used within the try…catch block otherwise it will return a null value. If we use nested try…catch block it returns the error_state value for the current scope.

Syntax

ERROR_STATE()

Example

1. A simple example to show how to use of ERROR_STATE() function.

DECLARE @int AS INTEGER
BEGIN TRY
    SET @int = 1/0.0
END TRY

BEGIN CATCH

    PRINT 'Error : ' + CAST(@@ERROR AS VARCHAR(20))
    PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(20))
    PRINT 'Error Message : ' + ERROR_MESSAGE()    
END CATCH

Output

pic0

SQL SERVER – ODBC Scalar Functions in T-SQL


The Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs).

Microsoft has standardized ODBC data source from SQL Server 2012 onwards. For more information, please refer this blog post.

ODBC is a standard definition of an application programming interface (API) used to access data in relational or indexed sequential access method (ISAM) databases. SQL Server supports ODBC, via the SQL Server Native Client ODBC driver. Learn more about SQL Server Native Client (ODBC).

In this blog post, I will be talking about ODBC Scalar Functions and its use in SQL Server.

Scalar Function Escape Sequence in ODBC

ODBC uses escape sequences for executing scalar functions.

Syntax

{fn scalar-function}

ODBC scalar function category

  • String Functions
  • Numeric Functions
  • Time, Date, and Interval Functions
  • System Functions
  • Explicit Data Type Conversion Function
  • SQL-92 CAST Function

Download ODBC Scalar Functions cheat sheet. This document is created for my personal reference. But, you can download this document without any restrictions.

So, we have learned about ODBC, scalar function escape sequence in ODBC and scalar functions. Now, I am going to talk about how to use ODBC scalar functions in the SQL statements.

ODBC Scalar Functions in T-SQL

We can use ODBC Scalar Functions in Transact-SQL statements. These statements are interpreted by SQL Server automatically. These functions can be used in stored procedures and user-defined functions.

T-SQL Syntax

{fn <function_name> [ (<argument>,….n) ] }

In simple words, we must use ODBC scalar function escape sequence and we can include any ODBC scalar functions inside the escape sequence.

Example

1. Print the current date and time using ODBC scalar function

select {fn now()} as DataTime

2. String concatenation function

Declare @str1 as varchar(100), @str2 as varchar(100)
set @str1 = ‘SQL SERVER’
set @str2 = ‘ RIDER’

select {fn CONCAT(@str1, @str2)} as BlogName

pic1These simple examples are given here to help you to understand the use of ODBC scalar function in T-SQL. I hope you have understood the concept of the ODBC and its functions usage in SQL Server.

SET Statements And Its Active zone – Pharse Time and Run-time


SET Statements are used to control the session of the SQL Server. There are some settings that are set at instance level or database level. So, whenever we initiate a connection to the SQL instance, it applies all the setting to the new connection and each user will get separate session with all settings applied by default. We can also change the server level setting and it is done by administrator only and the changes will affect to all the connections made to that instance. There are some times that we need to apply certain settings to the current connection or session and it should not affect other connections at the same time. In this scenario, we use explicit set statements. There are various categories of set statement available in SQL Server those are given in the below image.

pic1These set statements will change or override the existing server level or database level settings when it is used in the SQL connection. These set statements are applied in two zones, they are Execution or Run time or Parse time.

Parse time set statements

The following statements are used in the parse time of the SQL statements.

SET PARSEONLY
SET FIPS_FLAGGER
SET OFFSETS
SET QUOTED_IDENTIFIER

Execution or Run time set statements

These statements are taking effect at execution time only

SET DATEFIRST
SET DATEFORMAT
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET IDENTITY_INSERT
SET LANGUAGE
SET ARITHABORT
SET ARITHIGNORE
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT

These statements are very helpful while executing a batch statement or creating stored procedure. These set statement are used in different ways some with just ON or OFF switch and some of them need integer or time value. Learn about all set statements in this website. I have written about each set statement with detailed example and some of them are available in videos in YouTube channel. So, feel free to browse all the content and share your feedback.