Contents tagged with MS SQL

  • New SQL Server 2016 features to use in Acumatica

    Hello everybody,

    today I want to write about new additions for SQL Server T-SQL language, which are there starting from 2016. It is create or alter syntax.

    In the past, if you've made some custom SQL view, it was needed to have constructions like this ( pseudo code ):

    if view ( stored procedure, function, trigger ) exists

         create view ( stored procedure, function, trigger )


       alter view ( stored procedure, function, trigger )

    But startring from 2016 SP1 you can use following syntax ( pseudo code ):

    Create or alter view ( stored procedure, function, trigger ).

    Or in code form it may look like this:

    create or alter procedure yourProcedure



     print (1)


    go … more

  • How to create unique index in MS SQL that will allow NULL

    Hello everybody,

    today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null. 

    How to achieve it? Following T-SQL can do this:

    CREATE UNIQUE INDEX idx_studentcardid_notnull ON dbo.Students(studentcardid) WHERE studentcardid IS NOT NULL;

    in this example MS SQL will create Unique index, but that index will be applied only to non null values, while null values will be ignored by WHERE condition. more

  • What is Object_id in MS SQL

    Hello everybody,

    today I want to leave a short notice on OBJECT_ID in MS SQL.

    Quite often I've seen sql like this:

    IF OBJECT_ID(N'dbo.Students', N'U') IS NOT NULL DROP TABLE dbo.Students;

    Today I have found time to discover what it actually means. 

    OBJECT_ID is function, that checks wheather object exists or not. It accepts two parameters: object name and object type. In my case object name is dbo.Students, object type U is internal representation of table.


  • Performance difference in Acumatica between MS SQL and MySQL

    Hello everybody,

    today I want to write a few words about performance difference between MySQL and MS SQL in Acumatica.

    Relatively recently Acumatica provided possibility to use MySQL as it's database. For some unknown reason

    I didn't notice anybody who decided to use MySQL as their database solution. If you know any, please let me know,

    how they like their experience. 

    For me as developer it become interesting to check performance of MySQL vs MS SQL. Especially from standpoint of servers. 

    Take note on how one of mine task managers look like:

    as you can see I have 40 logical cores or 20 physical cores. Try to guess what is price of license for MS SQL for using at such a computer? … more

  • How to make MS SQL stored procedure that is protected from SQL Injection

    Hello everybody,

    today I want to write a simple note on how to make stored procedures in MS SQL server which are protected from SQL injections. 

    Below goes example of SQL stored procedure, that is vulnerable to SQL injection attacks:

    -- Bad code, don't use it ever

    CREATE PROCEDURE SearchCustomers

    @searchCust varcahr(50)



    DECLARE @query VARCHAR(100)

    SET @query = 'SELECT * FROM Customer WHERE NAME LIKE ''%' + @searchCust + '%'''

    EXEC (@query)


    Why it is bad? Because if somebody passes into @searchCust value or 1=1 --- then SQL will return all customers. If to add a bit more creativity it's possible to get from that database plenty of information.

    If you wonder, … more

  • How to make back up of db smaller in MS SQL

    Hello everybody,

    today I want to document one hack that preserved me plenty of space on my hard drive.

    Recently I've tried to make back up of production database and was shocked to see that it had almost 200 Gb on my hard drive.  So it made me wonder how can I have smaller backups. 

    And here is the solution.

    In Microsoft SQL Server Management Studio click on database, and choose new query

    enter there following command: sp_configure 'backup compression default', 1

    then this: reconfigure WITH OVERRIDE;

    After such manipulations I've got back up that had only 27 Gb. more

  • Hierarchy for CTE in MS SQL

    Hello everybdoy,

    today I want to write some notes about hierarchy research in ms sql database. 

    Imagine you have following structure in your table 


    id               |           rootID   |     some additional fields

    1                |            1          |      ----

    2                |            1          |      ----

    3                |            2          |      ----

    4                |            2          |      ----

    5                |            2          |      ----

    and image that you need to get info like who is below 1. 

    for this purpose following sql can be useful:

    -- wrap up tree selection into tvf

    create function HierarchyFunction(@ … more

  • How to clean plan query cache

    Hello everybody,

    some sql notes:

    dbcc freeproccache; this one will clean plan of cached of sql.

    Another note of how to find Sql code and plan by id from dm_exec_cached_plans

    create function SqlAndPlan(@handle varbinary(max))

    returns table


    return select sql.text, cp.usecounts,cp.cacheobjtype,

    cp.objtype, cp.size_in_bytes,



     sys.dm_exec_sql_text(@handle) as sql cross join

     sys.dm_exec_query_plan(@handle) as qp

     join sys.dm_exec_cached_plans as cp

     on cp.plan_handle = @handle;

    then you can use it in the following way:

     select * from SqlAndPlan(0x06650D007852A00B40613C82000000000000000000000032)

    And in order to see sql and plan simultanously you can use … more

  • Query plan in MS SQL

    Hello everybody,

    some MS SQL internals.

    Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans. 

    For example like this:

    select * from sys.dm_exec_cached_plans

    this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?

    Take look at the screenshot :

    the area of interest for us is column plan_handle. 

    You can use it for another sql request. Like this:

    select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);

    if you click at column with xml you'll see the following picture:

    How to interpret those … more

  • SQL Formatting tool

    Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.

    For example like this:

    exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine1, APAddress.AddressLine2, APAddress.AddressLine3, APAddress.City, APAddress.CountryID, APAddress.State, APAddress.PostalCode, APAddress.IsValidated, APAddress.tstamp, APAddress.CreatedByID, APAddress.CreatedByScreenID, APAddress.CreatedDateTime, APAddress.LastModifiedByID, APAddress.LastModifiedByScreenID, APAddress.LastModifiedDateTime FROM APAddress APAddress WHERE (APAddress.CompanyID = 2) AND … more