European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2016 Hosting - HostForLIFE.eu :: How to Calculate or Find median in SQL Server?

clock June 16, 2016 21:20 by author Peter

Today, with this post we explain that how to calculate median in SQL Server or function to find median in SQL Server. Before we calculate/find median in sql server first we understand the what is the median and formula to calculate the median in sql server.

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it. To calculate or Find median grou by in SQL Server click here Calculate median group by in SQL Server. For ex: supposed to find the median of bellows values like:
{1,2,3,4,5} then the median is equal 3
{1,2,5,10} then the median is equal 3.5
{1,5,8,7} then median is equal 6

So how to calculate median in SQL Server query, below is the query to find the median

Query:
SELECT CONVERT(FLOAT,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Calculate or Find median in SQL Server?

clock June 16, 2016 21:20 by author Peter

Today, with this post we explain that how to calculate median in SQL Server or function to find median in SQL Server. Before we calculate/find median in sql server first we understand the what is the median and formula to calculate the median in sql server.

Median means denoting or relating to a value or quality lying at the midpoint of a frequency distribution of observed values or quantities, such that there is an equal probability or falling or below it. To calculate or Find median grou by in SQL Server click here Calculate median group by in SQL Server. For ex: supposed to find the median of bellows values like:
{1,2,3,4,5} then the median is equal 3
{1,2,5,10} then the median is equal 3.5
{1,5,8,7} then median is equal 6

So how to calculate median in SQL Server query, below is the query to find the median

Query:
SELECT CONVERT(FLOAT,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server Hosting - HostForLIFE.eu :: Repair SQL Database From Suspect Mode

clock June 14, 2016 20:11 by author Anthony

Sometimes we have to face a critical situation when SQL Server database going to Suspect Mode. In that moment no work can be done on database. Database may go into suspect mode because the primary file group is damaged and the database cannot be recovered during the startup of the SQL Server
Reason for database to go into suspect mode:

Free ASP.NET Hosting - Europe

  • Data files or log files are corrupt.
  • Database server was shut down improperly
  • Lack of Disk Space
  • SQL cannot complete a rollback or roll forward operation

    
How to recover database from suspect mode:

  • Change the status of your database. Suppose database name is “BluechipDB”?

EXEC sp_resetstatus '';
Example:
EXEC sp_resetstatus 'BlueChipDB'

  • Set the database in “Emergency” mode

ALTER DATABASE  SET EMERGENCY;
Example:
ALTER DATABASE BlueChipDB SET EMERGENCY

  • Check the database for any inconsistency

DBCC CHECKDB('');

Example:
DBCC checkdb('BlueChipDB')

If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you need not execute the following query.

ALTER DATABASE  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Example:
ALTER DATABASE BlueChipDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  • For safety, take the backup of the database.
  • Run the following query as next step.Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone.
  • There is no way to go back to the previous state of the database.
  • So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS);

Example:
DBCC CheckDB ('BlueChipDB', REPAIR_ALLOW_DATA_LOSS)

  • Finally, bring the database in MULTI USER mode

ALTER DATABASE  SET MULTI_USER;
ALTER DATABASE [BlueChipDB]  SET MULTI_USER

  • Refresh your database server and verify the connectivity of your database. Now users should be able to connect to the database properly. If any data loss, you can restore database – backup taken in step 5.

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL 2016 Hosting - HostForLIFE.eu :: How to Use Concat Function in SQL 2016?

clock May 3, 2016 00:52 by author Anthony

In this tutorial, I will explain how to use concat function in SQL 2016. CONCAT function is also known as T-SQL Function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.

Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Arguments

string_value: A string value to concatenate to the other values.

Return Types: String, the length and type of which depend on the input.

 

Interesting features

1. CONCAT takes a variable number of string arguments and concatenates them into a single string.

2. It requires at least two input values; otherwise, an error is raised.

3. All arguments are implicitly converted to string types and then concatenated.

4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.

5. The implicit conversion to strings follows the existing rules for data type conversions. 

6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

Examples:

Concatenate Space Characters with input variables

 

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

Declare @inpLastName Varchar(20) ='Carter'

 

---- Concatenating variables without Concat Function

Select @inpFirstName+' '[email protected] as FullName

FullName

John Carter

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName

FullName

John Carter

 

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

 

Concatenate Space Characters with with NULL values

Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

---- Set Null Value here

Declare @inpLastName Varchar(20) =NULL

 

---- Concatenating variables without Concat Function

Select @inpFirstName+' '[email protected] as FullName

FullName

NULL

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName

FullName

John

You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.

Concatenate number and string together

If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:

---- Declare local variables

Declare @inpFirstName Varchar(20) ='John'

Declare @inpContactNumber BIGINT =1234567890

 

---- Concating variables without Concat Function

Select @inpFirstName+':'+CAST(@inpContactNumber as VarcharasNameWithContact

 

NameWithContact

John :1234567890

---- Concatenating variables with Concat Function

Select CONCAT(@inpFirstName,':',CAST(@inpContactNumber as Varchar))as NameWithContact

 

NameWithContact

John:1234567890

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.

Conclusion

The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.

 

 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

 

 



SQL 2016 Hosting - HostForLIFE.eu :: How to Make Time Converter in SQL Server 2016?

clock April 22, 2016 00:06 by author Anthony

In this tutorial, I will explain about how to make time converter. The time converter converts the most common time units, second, hour, minute, day, week, month, year and century. Second is the SI base unit of time and officially defined as the duration of 9 192 631 770 periods of the radiation corresponding to the transition between the two hyperfine levels of the ground state of the caesium 133 atom.

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.


Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEPART(YEAR, @VARDT) - 1900 AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Or, we can use the below code.


DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEDIFF(YEAR, 0, @VARDT) AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
 + CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

I have added the given integer value in second part of the default datetime value (‘1900-01-01 00:00:00.000’) of SQL Server to generate a datetime value. Once we have a datetime data type, we can easily apply the DATEPART function to extract the required parts from that. In this scenario SQL Server automatically takes care of the date time conversions. Finally, I have subtracted the respective date time parts from the generated datetime data type value from their respective parts in the final SELECT statement wherever required.

In second logic, I have just used a DATEDIFF function for the first step computation which can take care of all the conversions specially in below scenarios smoothly. For example, we don’t need to worry about to year to month, month to day and day to hour conversion. We just need to use DATEDIFF function for the first part.

Convert number into month, day, hour, minute and second


SELECT CAST((DATEDIFF(MONTH, 0, @VARDT)) AS VARCHAR(10)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

13 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into day, hour, minute and second


SELECT CAST((DATEDIFF(DD, 0, @VARDT)) AS VARCHAR(10)) + ' day(s) '
 + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

406 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into hour, minute and second


SELECT CAST(DATEDIFF(HOUR, 0, @VARDT) AS VARCHAR(20)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'


Output:

9752 hour(s) 46 minute(s) 51 second(s)

Convert number into minute and second


SELECT CAST(DATEDIFF(MINUTE, 0, @VARDT) AS VARCHAR(25)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

 

 

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

 



SQL 2016 Hosting - HostForLIFE.eu :: Runtime For R Script

clock April 11, 2016 23:48 by author Anthony

In this tutorial, I will explain how to fix unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime, Fatal error: cannot create ‘R_TempDir’. R is the most popular programming language for advanced analytics today. You can use it for statistical analysis, uncover patterns, identify trends or even build predictive models. R has a massive collection of free packages on CRAN created by an ever-growing worldwide developer community.

In the latest SQL Server 2016 CTP3 release, new native in-database support for open source R and multi-threaded RevoScaleR functions are now available via R Services. SQL Server R Services provides a platform for using the powerful R language and package portfolio with a simple Transact-SQL interface. To overcome open source R’s performance and scale limitations, Enterprise Edition includes RevoScaleR features.

Data scientists, BI developers and applications can make parameterized calls to the R runtime from SQL code or stored procedures to get R computed result sets or data visualizations. In SQL Server 2016 CTP3, support for ad-hoc execution of R scripts via a new system stored procedure, sp_execute_external_script, was added. It will currently support pushing data from a single SELECT statement with multiple input parameters to R and return a single data frame as output.

  • Locate launcher config file (this is used by the MSSQL Launch pad service)
    • Its normally located in the Binn folder instance the which is available in the root directory in my case D: \ Program Files \ Microsoft SQL Server \ MSSQL 13.MSSQLSERVER \ MSSQL \ Binn
  • View the contents of rlauncher and check the working directory of the which is where we are having issues creating temp dir.
    • R does not like spaces in the working directory
    • R launcher 1

  • Create a folder in C:\ called temp “C:\temp”
  • Change the working directory to c:\temp
  • R launcher 2

  • Grant permissions for the service MSSQLaunchpad service account in my case its NT Service\MSSQLaunchpad
  • R launcher 3

    Restart the SQL server services and SQL Launch pad services.
    We can also fix this by using short paths by replacing the working directory with a short path. Example below cmd will give us the short path for the location cmd /c for %A in (“D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData”) do @echo %~sA

     

     

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Create a Date List from a Date Range in SQL Server

clock September 14, 2015 07:07 by author Rebecca

In this post, we will convert a given date of range into list of dates as per the business requirement. Just have a look on as these three mentioned demo table below which has three columns CustomerID, StartDate and EndDate. So, we want to generate the date list for the given date range for each customer. For example if we have a entry as CustomerID – “1”, StartDate – “10-Dec-2012” and EndDate – “19-Mar-2013”, then this should return a date list for CustomerID 1 with 4 rows. First row for 10-Dec-12 to 31-Dec-12, Second row for 01-Jan-13 to 31-Jan-13, Third row for 01-Feb-13 to 28-Feb-13 and fourth row for 01-Mar-13 to 19-Mar-13.

Mind that in first row, we have started with the actual start date of the given date range and in the last row, we have put the end date as actual end date. But, if there is any more row between first row and last row, it has a date range as whole month.

Look at this table of date range below:

And we want the required output from this date range is as below:

How to Generate The Output

Step 1

Create a table as below:

CREATE TABLE DateRange
(
CustomerID INT,
StartDate DATE,
EndDate DATE
)

Step 2

Insert demo values for customer and date range

INSERT INTO DateRange
VALUES (1, '10-Dec-12', '19-Mar-13'),
(2, '20-Mar-14', '10-Jul-14')

Step 3

Create another table to hold Serial number so that we can apply a join with this table to generate the date list for given date range. You can also use a demo DateList table to get the desired output. In this demo, we are using serial numbers to generate the desired output:

CREATE TABLE TableSerialNumber
(
RowNumber INT
)

Step 4

Insert serial numbers up to 100 for this demo using sys.columns table. You can also use another way to insert this.

INSERT INTO TableSerialNumber
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM SYS.COLUMNS

Step 5

Now, write a SQL query to extract the output as required. You can also try with some different way using DateList table too. Here, we're gonna use a serial number to generate the rows and date list dynamically for the given date range.

SELECT A.CustomerID
,CONVERT(VARCHAR(20), (CASE WHEN B.RowNumber = 1 THEN A.StartDate
ELSE DATEADD(MONTH, B.RowNumber - 1, DATEADD(DD, -(DATEPART(DD, A.StartDate)) + 1, A.StartDate)) END), 106) AS FromDate
,CONVERT(VARCHAR(20), (CASE WHEN (DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate))) < A.EndDate THEN
DATEADD(DD, -(DATEPART(DD, A.StartDate)), DATEADD(MONTH, B.RowNumber, A.StartDate)) ELSE A.EndDate END), 106) AS ToDate
FROM DateRange A
INNER JOIN TableSerialNumber B ON B.RowNumber <= (DATEDIFF(MONTH, A.StartDate, A.EndDate) + 1)

And you're done! You can also generate it in some other way too. For example, you can achieve this with a demo DateList table too. It's your creativity to find your own way.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: Using Dynamic Data Masking in SQL Server 2016

clock September 10, 2015 12:08 by author Rebecca

There is a long list of new features getting introduced in SQL Server 2016. In this post, we would talk about one of the security feature called Dynamic Data Masking. How to Use it?

Whenever you access your account on your bank site, would you be comfortable in seeing your credit card or bank account number in clear text on the web page? There are multiple ways to do this at the application level, but as a human nature, it leaves room for error. One small mistake from the developer can leak sensitive data and can cost a huge loss. Wouldn’t it be great if a credit card number would be returned with only its last 4 digits visible – XXXX-XXXX-XXXX-1234 with no additional coding? Sounds interesting, read on!

Before experimenting this feature please remember that if you are using CTP2.0 then you need to turn on trace flags using below command.

DBCC TRACEON(209,219,-1)

If you don’t enable, then here is the error which you would receive while trying this sample script given later.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘masked’.

Don't forget that this is SQL Server 2016 feature. Running the script on earlier version of SQL would cause below:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ‘MASKED’.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

And here is the script to create objects (database, table, masked column):

SET NOCOUNT ON
GO

Drop database MaskingDemo, if already exists

USE [master]
GO
IF DB_ID('MaskingDemo') IS NOT NULL
BEGIN
ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [MaskingDemo]
END

Create new database called MaskingDemo

CREATE DATABASE MaskingDemo
GO
USE MaskingDemo
GO

Create table with different data type columns

CREATE TABLE MyContacts (
ID INT IDENTITY(1, 1) PRIMARY KEY
,fName NVARCHAR(30) NOT NULL
,lName NVARCHAR(30) NOT NULL
,CreditCard VARCHAR(20) NULL
,SalaryINR INT NULL
,OfficeEmail NVARCHAR(60) NULL
,PersonalEmail NVARCHAR(60) NULL
,SomeDate DATETIME NULL
)

Insert a Row

INSERT INTO [dbo].[MyContacts]
([fName],[lName] ,[CreditCard],[SalaryINR],[OfficeEmail],[PersonalEmail], SomeDate)
VALUES('Rebecca','C','1234-5678-1234-5678',999999,'[email protected]','[email protected]', '31-March-2013')
GO

Apply Masking

ALTER TABLE MyContacts
ALTER COLUMN CreditCard ADD MASKED
WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-XX",2)')
ALTER TABLE MyContacts
ALTER COLUMN SalaryINR ADD MASKED
WITH (FUNCTION = 'default()')      -- default on int
ALTER TABLE MyContacts
ALTER COLUMN SomeDate ADD MASKED
WITH (FUNCTION = 'default()')      -- default on date
ALTER TABLE MyContacts
ALTER COLUMN fname ADD MASKED
WITH (FUNCTION = 'default()')      -- default on varchar
ALTER TABLE MyContacts
ALTER COLUMN OfficeEmail ADD MASKED
WITH (FUNCTION = 'email()')
GO

Create a new user and grant select permissions

USE MaskingDemo
GO
CREATE USER WhoAmI WITHOUT LOGIN;
GRANT SELECT ON MyContacts TO WhoAmI;

 

As we can see above, those fields which are masked are showing obfuscated data based on masking rule.
For your information, versions after CTP2 release, the trace flag will not be needed. If you add trace flag, you would start getting “Incorrect syntax” error.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: Query Store Features in SQL Server 2016

clock September 7, 2015 09:46 by author Rebecca

In this article, I'm introducing you new feature in SQL Server 2016 CTP 2.0 named Query Store. This is a very useful feature for the DBA and developers from the performance point of view.

Query store feature allows to captures multiple query plan for a query and run time statistics. Query store can store multiple execution plans per query, it can force query processor to use a particular execution plan which is referred as plan forcing using USE PLAN query hint.

By default, Query Store is not active so you can enable it in two ways:

Step 1

First Using SSMS, Right Click on DatabaseName -> Go to properties -> Query Store options -> Enable -> True

Step 2

Second way to enable it by using ALTER Database script in this manner:

ALTER DATABSE  Database_name SET QUERY_STORE = ON;

Query store option is not enabled for master or tempdb database. If you try to enable it then you get below error:

Msg 12420, Level 16, State 1, Line 1

Cannot perform action because Query Store is not started up for this database.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Step 3

To determine the current options available for query store we can query the system view sys.database_query_store_options. Query stores contains two stores:

  1. Plan store – Stores execution plan information
  2. Running Stats store – Stores execution statistics information



HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: Using JSON Support in SQL Server 2016

clock August 31, 2015 09:19 by author Rebecca

There's so many new capabilities to play around and explore in SQL Server 2016. One of the new capabilities that has caught our attention is the introduction of JSON support. In this post, I will get started in creating a JSON document using SQL Server 2016.

SQL Server 2016 will support JSON (JavaScript Object Notation). JSON is an open, text-based exchange format based on JavaScript’s object literal notation. JSON is a popular data-interchange format used in modern web and mobile applications, as well for storing unstructured data. It is supported in several DB/NoSQL engines and this improvement will enable developers to put some JSON processing logic in the SQL Server that will enable them to parse, query, analyze, and update JSON data.

Step 1

In this post, you will query a standard table and then convert it into JSON format. For this example, you are querying the standard AdventureWorks database:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]

Sample output for rows inside SQL Server Management Studio looks like below:

Step 2

Now, you will add the FOR JSON AUTO support to get the same output in JSON format.

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON AUTO

The output looks like below:

[
{
"PersonType":"EM",
"FirstName":"X",
"MiddleName":"Y",
"LastName":"Z",
"EA":[{"EmailAddress":"[email protected]"}]
},
{"PersonType":"EM",
"FirstName":"A",
"MiddleName":"B",
"LastName":"C",
"EA":[{"EmailAddress":"[email protected]"}]
}
]

Step 3

You can also generate JSON using the PATH option like:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON PATH

The output for this query would look little different like:

[
{"PersonType":"EM", "FirstName":"X”, “MiddleName":"Y”, “LastName":"Z”, “EmailAddress":"[email protected]"},{"PersonType":"EM”, “FirstName":"A”, “MiddleName":"B”, “LastName":"C”, “EmailAddress":"[email protected]"}
]

Now that you are able to generate few simple output using JSON keyword.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.




About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in