SQL SERVER – Beginning Table Valued Constructors – Notes from the Field #052

[Note from Pinal]: This is a 52th episode of Notes from the Field series. I am very happy that the journey which we started one year ago is a amazing milestone. In this 52 episode in the entire year we have learned a lot of new things from industry experts of LinchPin People. They are amazing sets of people who know what they are doing on the field and in the real world. I have received so many notes from blog readers that they have benefited from the experience shared by LinchPin Team.

In this episode of the Notes from the Field series database expert Kathi Kellenberger explains Table Valued Constructors. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively unknown topic for database experts. Read the experience of  Kathi in his own words.


Table Valued Constructors, also called Row Constructors, were introduced with SQL Server 2008, but many SQL Server professionals haven’t heard about them. They make it easy to insert multiple rows of hard-coded values into a table with just one insert statement.  NOTE: In this post, I’ll abbreviate Table Valued Constructors as TVCs.

I’ll often see people using older techniques to populate sample tables when asking for T-SQL help on the forums. The following example demonstrates two techniques.

CREATE TABLE #test(Col1 INT, Col2 VARCHAR(10));
--Method 1, using UNION
INSERT INTO #test(Col1, Col2)
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c';

--Method 2, multiple select statements
INSERT INTO #test(Col1, Col2)
SELECT 4, 'd';
INSERT INTO #test(Col1, Col2)
VALUES( 5, 'e');

The first method takes advantage of the UNION ALL operator to combine three SELECT queries into one set of results.  The second example uses a separate INSERT statement for each row to be inserted. One statement uses a SELECT with hard-coded values, while the second uses the VALUES keyword.

Beginning with the 2008 version, you can specify multiple rows with the VALUES syntax which is now called TVC. Each set of values must be within parentheses and each set must be separated by a comma. Example 2 demonstrates this technique.

CREATE TABLE #testTVC(Col1 INT, Col2 VARCHAR(10));
INSERT INTO #testTVC(Col1, Col2)
VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

The big advantage of TVCs is that you save typing. Of course, looking cool on the forums is a bonus.

While using the TVC to insert multiple rows with one insert statement is pretty fantastic, there are some other interesting uses. You can create a set of results using a TVC within a derived table. Take a look at this example:

SELECT *
FROM
(VALUES ('January'),('February'),
(
'March'),('April'),('May'),
(
'June'),('July'),('August'),
(
'September'),('October'),
(
'November'),('December')
)
AS Months ([Month]);

 

In the months example above, my TVC is part of the FROM clause. Notice that I must alias the TVC just like a derived table. I also have to provide column names right after the alias.

Another interesting use of TVCs involves the APPLY operator. A feature of APPLY is that columns from the outer query are visible inside the APPLY. Not only can you have hard-coded values as in the previous example, you can use columns from the outer query as well. The following example demonstrates this technique.

SELECT SOD.SalesOrderID, Dates.*
FROM Sales.SalesOrderHeader AS SOD
CROSS APPLY(VALUES('Order Date',OrderDate),
(
'Due Date',DueDate),
(
'Ship Date',ShipDate)) AS Dates(TypeOfDate,TheDate);

In this example, using the AdventureWorks database, each row from the SalesOrderHeader table is returned three times, once for each kind of date. Instead of displaying each date in its own column. This example “unpivots” the dates.

Table Valued Constructors can save you some typing and make your code look more organized. And, as you have seen here, they are also capable of some neat little tricks.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076

It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight.

The question is in the form of video which is displayed here. Watch the video and answer correct question in the comment on the this blog. You have 48 hours from the time of publishing this blog to answer it correctly.

If you due to any reason, you can’t watch the video, here is the image taken from the video. I strongly suggest you watch this video to get a clear idea of the puzzle.

In the above image, when I execute first select statement, it returns me answer as 0 but when I execute the second function, it gives me answer as 1. What is passed in the second select statement in ISNUMERIC function which is producing answer as 1, even though it is not visible.

Please leave your answer as a comment to this blog post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Exploring SQL Auditing with SQL Compliance Manager

As database administrators, we are constantly quizzed by our superiors to answer questions such as: how secure is our network, how secure is our data, is data secure at rest, is data secure in transition, who is accessing my data? All these are basic yet compelling queries businesses ask. In this age of competitive businesses, organizations are becoming tech savvy in building a secure fort for their critical data. Having worked on many projects in the past, these security measures are something I know are non-negotiable from an implementation point of view for administrators. When on this topic, I am reminded of a conversation I had with a junior DBA named Siva.

Siva: Hi Pinal!

Pinal: Hi Siva, how are you dude?

Siva: I am rocking and have been put onto a spot now from my internal auditing team.

Pinal: Wow, nice. Please shoot your question. Let me see what can be done.

Siva: Our internal auditing team wants to do auditing for our mission-critical sensitive database.

Pinal: Well, it’s easy and as simple as the question you asked.

Siva: Really, are you sure?

Pinal: Yes. Use the out-of-box capability of SQL Server auditing and it is something to play with too.

Siva: Oh, yeah. That is true. But there is a twist. They do want to log everything, but want to make sure our administration/maintenance activities are not logged per se.

Pinal: That is an interesting requirement for sure. But, it is not difficult either – trust me.

Siva: Now you are getting me interested. Please guide me.

Pinal: Well, start looking for filtered audit. Have you looked into it?

Siva: Oh yeah. I do need to look into it. Thanks for the pointer. If you get a chance, please write about these on your blog someday.

Pinal: Sure.

This conversation has been on my mind for quite some time and I have been wanting to write what Siva asked since then.

Simple Auditing Filtering with SQL Server

In the above conversation, I am making an assumption that the administrative tasks are done by the local administrator account like “sa.” The first step is to know the “principal id” of our administrator account. Use the following DMV to know the same:

SELECT name, principal_id FROM sys.server_principals

In our example, the “sa” account will have the principal_id of 1. So that is what we will use in our filter section of audit definition. The next is the audit definition we will create using the filter condition. A typical TSQL construct would look like:

CREATE SERVER AUDIT [Filtered-Audit-2014-10]
TO FILE
(  FILEPATH = N'C:\Temp\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
-- Change server_principal_id as needed
WHERE ([server_principal_id]<>(1))
GO

This is an awesome capability introduced with SQL Server 2012 and is worth a mention today. Now we can go ahead and create a server audit for specific events. Once the audit is enabled, we can go ahead and attach our events to this using the TSQL like below:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141020]
FOR SERVER AUDIT [Filtered-Audit-2014-10]
ADD (FAILED_LOGIN_GROUP)
GO

In this example, we have gone ahead and created an audit for failed logins. The wish list was to automate for a broader auditing capability with SQL Server.

Advanced Auditing with SQL Compliance Manager

On the topic of auditing I would like to talk a little bit about SQL Compliance Manager from Idera. As much as we would like to customize and use the out-of-box T-SQL functionality, it is sometimes necessary to do the same functionality of auditing in an automated fashion. This is where SQL Compliance Manager comes into play. There are a number of things we can start auditing using this tool. Various configuration options you can do with this tool include:

  1. Logins
  2. Failed Logins
  3. Security Changes
  4. Database Definition
  5. Administrative Activities
  6. User Defined Events

In addition to these, we can audit based on filtered access or we can audit all the events by unchecking the checkbox too. I don’t think we can get an easier configurable option for auditing logins, DDL, admin tasks in such a user friendly format. This in my opinion is the fastest way to configure audits on important tasks.

If you are a savy DBA, then the chances are you can enable all these auditing capabilities using a similar T-SQL script. But this is where I felt there are nice additions to the tool that make it worth a second look.

I particularly want to call out the auditing threshold, which can be used to track abnormal activity and proactively alert a DBA of any suspicious activity that has been identified based on thresholds set by the organization around access, login failures, DDL activity and many more. In addition to those activities, there is also a laundry list of standard reports they generate after the data is collected. This list is pretty exhaustive to say the least.

The more I play with the product, there are more pleasant surprises with each additional screen.

Wrap up

As I wrap up, feel free to use the filtered auditing capability with SQL Server and explore how these can be controlled at a granular level. If you want a quick solution that will enable auditing, keeping security and compliance in mind, then it is worth looking into SQL Compliance Manager too. It is worthwhile to trial SQL Compliance Manager for your environment. I still look forward for plugs to the current SQL Server enhancements like filtered auditing, audit resilience capabilities in the future. I am sure these are present in some shape or form today and may be explored.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Embarcadero DB PowerStudio Promotion & Savings

If you are regular readers of this blog, you may be aware of that I use many different tools in my daily routine. One of them is DB PowerStudio for Embarcadero. The team has recently announced special saving for up to  51% when you buy the DB PowerStudio bundle. With DB PowerStudio, you get a database administration and development tool suite that enables you to work seamlessly across database platforms. DBAs and Developers need easy-to-implement and easy-to-use tools that enable them to create and maintain mission-critical databases, expedite code development, streamline database change management processes, and quickly optimize performance bottlenecks.

Here are some details about DB PowerStudio’s Bundle. It contains four products.

DBArtisan DBArtisan

  • Database administration and management
  • Space, data, security and performance management and insight
  • Schema and data migrations

Rapid SQL

  • The Intelligent IDE for SQL development
  • Query building
  • Debugging SQL code, function and stored procedures

DB Optimizer

  • Automated SQL profiling, tuning, optimization
  • Proactive identification of performance issues and bottlenecks
  • Maintain database uptime and availability

DB Change Manager

  • Essential schema and data synchronization
  • Quickly roll-out database changes
  • Visibility and reporting on DB changes
  • Compliance with database audit and reporting requirements

You can click here to avail a 51 % discount.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Flip Value of Bit Field in SQL Server? – Part 2

Yesterday I wrote a blog post about SQL SERVER – How to Flip Value of Bit Field in SQL Server? . Lots of good suggestions I have received in response of the same. Here are a few different methods to flip value of a bit field suggested by my very good friend Vinod Kumar. All the methods are also very simple and flips the value of the bit field in SQL Server.

DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT @field1 Field1, @field2 Field2
-- Method one
SELECT @field1 = 1^@field1, @field2 = 1^@field2
SELECT @field1 Field1, @field2 Field2
-- Method 2
SELECT @field1 = 1-@field1, @field2 = 1-@field2
SELECT @field1 Field1, @field2 Field2
-- Method 3
SELECT @field1 = (1+@field1)%2, @field2 = (1+@field2)%2
SELECT @field1 Field1, @field2 Field2
-- Method 4
SELECT @field1 = ABS(@field1-1), @field2 = ABS(@field2-1)
SELECT @field1 Field1, @field2 Field2

Though Vinod does not get any award for suggesting alternative methods. If you suggest an interesting solution which does not use CASE statement, you can win a month long Pluralsight subscription.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Flip Value of Bit Field in SQL Server?

How do you flip bit field in SQL Server?

Well, if you search for this question on internet, you will find many different answers, today we will see my favorite method to flip value for the bit field.

Bit field can hold two different values zero (0) and one (1). When we change the value of zero (0) to one (1) or change the value of one (1) to zero (0) it is called as flipping the value of the bit field.

You can just flip the value of the bit field by prefixing it with ~ before it. Here is a simple script for the same.

DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT @field1 Field1, @field2 Field2
SELECT @field1 = ~@field1, @field2 = ~@field2
SELECT @field1 Field1, @field2 Field2

You can see in above script the bit variable changes value with the help of ~ (tilda).

There are many different ways to flip the value of bit field. What is your favorite method?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database Stuck in Restoring State

Here is a one of the very common question I keep on getting via email.

“I just restored all the backup files of my database, however, my database is still stuck in restoring state. How do I fix it?”

Well, if you have already restored all the database, you can execute the following code and it will bring your database from recovery to operational state.

RESTORE DATABASE NameofDatabase
WITH RECOVERY

If due to any reason, above query returns error about restoring log files or any other file and you do not have that file in your hand, you can run following command.

Remember above command will roll forward your database and you will be not able to restore any other database after that.

RESTORE DATABASE NameofDatabase
WITH RECOVERY,REPLACE

If you are not sure what to do, leave a comment and I will help you out with your situation.

Reference: Pinal Dave (http://blog.sqlauthority.com)