News in the Category: SQL Subscribe to the rss of this category.

Total posts: 6914 | Sort by Views | Sort by Hits

Interview Question of the Week #030 Retrieve Last Inserted Identity of Record

Journey to SQL Authority with Pinal Dave, August 2, 2015
Views: 45 | Hits 2

Question: What are the different ways of retrieving the identity of last inserted record? Which method do you prefer? Answer: There are many different ways to do that. Here are three different ways to...

SQL SERVER How to Remove All Characters From a String Using T-SQL?

Journey to SQL Authority with Pinal Dave, August 1, 2015
Views: 51 | Hits 3

In the recent past I have seen the best interaction and learning happens when we all get into a puzzle and challenge mode. And that just gets the creative juice in our minds and some really innovative...

SQL SERVER Puzzle Inside Working of Datatype smalldatetime

Journey to SQL Authority with Pinal Dave, July 31, 2015
Views: 67 | Hits 2

In the recent past, I have been bringing few puzzles around using date and time functions. All these have brought some awesome feedbacks and you all have been sharing some awesome answers. Todays ques...

SQL SERVER DevOps for the DBA Notes from the Field #091

Journey to SQL Authority with Pinal Dave, July 30, 2015
Views: 76 | Hits 2

[Note from Pinal]: This is a91st episode of Notes from the Fieldseries.Divas is a new buzz word with lots of substance behind it. Not everyone understands what it means and not everyone actually relat...

SQL SERVER Error: Msg 245 Conversion failed when converting the varchar value Inactive to data type int

Journey to SQL Authority with Pinal Dave, July 29, 2015
Views: 75 | Hits 1

Using CASE statements is something I see a lot of developers use. They use it in complex environments and I have been fortunate to troubleshoot and look at code blocks that run for pages using the sta...

SQL Server Text Search

The Code Project Latest Articles, July 28, 2015
Views: 73 | Hits 9

A stored procedure that searches for a text in the SQL Server database (DDL)...

SQL SERVER Free SQL Complete Add-in For SSMS

Journey to SQL Authority with Pinal Dave, July 28, 2015
Views: 102 | Hits 10

This article covers main features of ApexSQL Complete, free SQL complete add-in. You can download the most recent version, and check its features while reading the article. ApexSQL Complete is a free ...

SQL SERVER Inserting into Sparse Column Sets and Errors Associated With It

Journey to SQL Authority with Pinal Dave, July 27, 2015
Views: 66 | Hits 3

I have written tons of blogs in the past around using sparse columns. The best part and lesser known part of sparse columns is that we can write to the column sets directly too. In this blog let me ta...

Interview Question of the Week #029 Difference Between CHARINDEX vs PATINDEX

Journey to SQL Authority with Pinal Dave, July 26, 2015
Views: 85 | Hits 3

Question:What is the difference betweenCHARINDEX vsPATINDEX? Answer:We can use either CHARINDEX or PATINDEX to search in a TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the sta...

SQL SERVER How to Find If Queries are Run in Parallel?

Journey to SQL Authority with Pinal Dave, July 25, 2015
Views: 92 | Hits 5

Technology innovations over years have made personal computing and the infrastructure inside our datacenters even more powerful. Gone are the days when our laptops used to come with single processors ...

Microsoft and Database Lifecycle Management (DLM): The DacPac

Simple Talk, July 24, 2015
Views: 47 | Hits 3

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an alternative way to automate the process of scripting out, or deploying a SQL Server d...

SQL SERVER FIX Property IsLocked is Not Available for Login

Journey to SQL Authority with Pinal Dave, July 24, 2015
Views: 103 | Hits 1

I play and experiment with SQL Server to the max. Sometimes I am not sure what I did and I land into something undesired. Then the reason to troubleshoot starts and I get to learn something new. Somet...

SQL SERVER What are T-SQL Window Functions? Notes from the Field #090

Journey to SQL Authority with Pinal Dave, July 23, 2015
Views: 112 | Hits 5

[Note from Pinal]:In this episode of the Notes from the Field series database expert KathiKellenbergerexplains about T-SQL Median. Kathi is an amazing instructor, she was the SQL author I have read in...

SQL SERVER Generating Meaningful Test Data with dbForge Data Generator for SQL Server

Journey to SQL Authority with Pinal Dave, July 22, 2015
Views: 93 | Hits 5

Recently I faced with necessity of generating meaningful SQL data for testing purposes. During my search for a proper application for the task, I found out that Devart, a recognized developer of datab...

SQL SERVER FIX Server principal Login Name has granted one or more permission(s). Revoke the permission(s) before dropping the server principal

Journey to SQL Authority with Pinal Dave, July 21, 2015
Views: 87 | Hits 1

There are parts of SQL Server where I always like to take expert advice. Sometimes a quick call to a friend can save you tons of time. When I see topics around AlwaysOn, though I know what the basic s...

SQL SERVER Fix Error: Msg 468, Level 16, State 9, Line 1

Journey to SQL Authority with Pinal Dave, July 20, 2015
Views: 82 | Hits 2

Ever since I have started writing about Error Messages a number of you ping me from time to time to understand why you are getting an error. As I always say, Error messages are a great way to learn co...

Interview Question of the Week #028 Few SQL Best Practices

Journey to SQL Authority with Pinal Dave, July 19, 2015
Views: 104 | Hits 9

Just last week, I moderated a SQL Server interview and I heard very interesting conversation. Question –What are the best practices are you following for SQL Server? When I heard this question – I tho...

PowerShell: How to Find Logical and Physical Processors on SQL Server?

Journey to SQL Authority with Pinal Dave, July 18, 2015
Views: 106 | Hits 4

Working on PowerShell scripts have been an interesting journey. I have written a couple of posts before on using WMI events. Was pondering on this thought and this blog is inspired by combining using ...

Can Code Review Be Automated?

Simple Talk, July 17, 2015
Views: 61 | Hits 4

I was scanning the API of DacFx, the ‘engine’ of SSDT, and became interested in the facility it contains for automating SQL code reviews. DacFx allows you to parse the SQL code sufficiently to do stat...

SQL SERVER Puzzle DISTINCT Values in NVARCHAR

Journey to SQL Authority with Pinal Dave, July 17, 2015
Views: 99 | Hits 5

The technique used in this blog is explained in earlier blog SQL SERVER Creating Dataset Using VALUES Clause Without Creating Table. To read more about Collation and Unicode refer to MSDN. This topic...

SQL SERVER Enhancing Reference Data in Master Data Services Notes from the Field #089

Journey to SQL Authority with Pinal Dave, July 16, 2015
Views: 81 | Hits 1

[Note from Pinal]: This is a 89th episode of Notes from the Fieldseries. Master Data Services is one of the most important, but a very little explored feature ofSQL Server.If you have been reading thi...

Generic ListHelper Class - .NET 4.5

The Code Project Latest Articles, July 15, 2015
Views: 102 | Hits 8

This tip will show how to sort a list of a particular class type from a SqlDataReader object by dynamically creating a lambda expression to carry out the process of sorting the list....

Generic ListHelper Class - .NET 4.5

The Code Project Latest Articles, July 15, 2015
Views: 85 | Hits 7

This article will show how to create and/or a list of a particular class type from a SqlDataReader object by dynamically creating a lambda expression to carry out the process of populating the list....

SQL SERVER Process ID X was killed by hostname ABC, host process ID Y

Journey to SQL Authority with Pinal Dave, July 15, 2015
Views: 114 | Hits

Errorlogs are an awesome place to learn something new every time. It springs up with some new errors which you might have not seen ever. I am a big believer of the fact that one needs to monitor and t...

SQL SERVER Querying Performance Counters from SQL Server

Journey to SQL Authority with Pinal Dave, July 14, 2015
Views: 98 | Hits 8

Troubleshooting SQL Server is something almost every single DBA has to go through in their lifespan. It sounds simple and often we are clueless on where to start. Here is a scenario, you have been tol...

SQL SERVER How to Change Server Name?

Journey to SQL Authority with Pinal Dave, July 13, 2015
Views: 122 | Hits 5

In recent past I have renamed my VM and wanted to make sure that everything is fine. This renaming process is also needed for changing the server names called inside of SQL Server too. So I searched t...

Interview Question of the Week #028 T-SQL Script to Detect SQL Server Version and Property

Journey to SQL Authority with Pinal Dave, July 12, 2015
Views: 106 | Hits 3

Here is a very basic question I just came across the other day. I was interviewing nearly 20 candidatesand I found that only 5 of them could write the query (with the help of the internet). Question: ...

POSTGRESQL How to Create Function? How to Declare Local Variable

Journey to SQL Authority with Pinal Dave, July 11, 2015
Views: 103 | Hits 2

I have written few courses on PostgreSQL on Pluralsight. You can watch list of all the courses over here. Recently, after watching my courses one of the user who is familiar with SQL Server but just b...

SQL SERVER Puzzle Working with functions to Concatenate columns

Journey to SQL Authority with Pinal Dave, July 10, 2015
Views: 92 | Hits 9

Every now and then I get pulled into coding review sessions and those are some of the fun times I every have working with T-SQL of any sorts. This blog is inspired by one such time where I saw at a cu...

SQL SERVER Using Project Connections in SSIS Notes from the Field #088

Journey to SQL Authority with Pinal Dave, July 9, 2015
Views: 98 | Hits 2

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Using Project Connections in SS...

SQL SERVER 2016 FIX: Install Rule Oracle JRE 7 Update 51 (64-bit) or higher is required failed

Journey to SQL Authority with Pinal Dave, July 8, 2015
Views: 135 | Hits 2

I enjoy spending time with my daughter almost every single day. It keeps me busy at home and most importantly, some quality time with her allows me to relax after a long day at work. But there one thi...

SQL SERVER Identifying Blocking Chain Using SQL Scripts

Journey to SQL Authority with Pinal Dave, July 7, 2015
Views: 101 | Hits 6

There are a number of blog posts that have been written on this very topic. This can be from basics to almost advanced in many ways. I am outlining some of them here below: SQL SERVER Quickest Way to...

SQL SERVER Creating Dataset Using VALUES Clause Without Creating A Table

Journey to SQL Authority with Pinal Dave, July 6, 2015
Views: 134 | Hits 9

You know that you can add data to a table using VALUES clause. But did you know that you can create a dataset using VALUES clause like a table without adding into another table? Suppose you want to cr...

Interview Question of the Week #027 Move TempDB from One Drive to Another Drive When Low Disk Space

Journey to SQL Authority with Pinal Dave, July 5, 2015
Views: 112 | Hits 3

I am often invited to attend various interviews and once in a while, I see practical questions discussed in the interview. Here is one of the important interview question which is related to TempDB. Q...

SQL SERVER Secure by Default The Mantra

Journey to SQL Authority with Pinal Dave, July 4, 2015
Views: 148 | Hits 7

Generally the talks I give at conferences revolve around performance and tuning. These are always the most sought after topic and I dont disappoint the requests because they give me an opportunity to ...

The Railmap Towards Easier Query Tuning?

Simple Talk, July 3, 2015
Views: 76 | Hits 2

Recently, I’ve been getting more and more engrossed in SQL Server Execution plans for various reasons, including editing the forthcoming third edition of Grant Fritchey’s book. There are certain infog...

SQL SERVER Difference Between Read Committed Snapshot and Snapshot Isolation Level

Journey to SQL Authority with Pinal Dave, July 3, 2015
Views: 174 | Hits 12

It is quite common that when a new feature gets introduced, there is a lot of talk about them. Sometimes even the old capabilities are lesser known because they can be used interchangeably in conversa...

SQL SERVER Why Havent You Disabled CPU Power Saving Yet?! Notes from the Field #087

Journey to SQL Authority with Pinal Dave, July 2, 2015
Views: 134 | Hits 5

[Note from Pinal]: This is an 87th episode of Notes from the Fieldseries. Every week, I personally wait for the notes from the fields from Mike because it containslessons of life which directly impact...

SQL SERVER Steps to Generate Windows Cluster Log?

Journey to SQL Authority with Pinal Dave, July 1, 2015
Views: 108 | Hits 6

Every now and then I sit next to DBA to learn some of their tricks. One of this recent learning is what I plan to share here. It is tough to learn everything all by ourselves, so learning is best done...

SQL SERVER Knowing the Source Application Using APP_NAME() Function

Journey to SQL Authority with Pinal Dave, June 30, 2015
Views: 122 | Hits 3

APP_NAME() function in SQL Server returns the application name of the current session in which it is called. When you execute the following code SELECT APP_NAME() AS application_name You get the follo...

SQL SERVER Getting started and creating JSON using SQL Server 2016

Journey to SQL Authority with Pinal Dave, June 29, 2015
Views: 122 | Hits 16

With SQL Server 2016 CTP out, I know many have started to play around with it and exploring some of the new capabilities. One of the new capabilities that has caught my attention is the introduction o...

Interview Question of the Week #026 64 Bit Vs 32 Bit Confusion

Journey to SQL Authority with Pinal Dave, June 28, 2015
Views: 123 | Hits 15

I often see people getting lost between 64 bit and 32 bit operating systems and application conversation. There are plenty of individuals still today, not sure what is the difference between them as w...

SQL Server 2016 New Feature: Dynamic Data Masking

Journey to SQL Authority with Pinal Dave, June 27, 2015
Views: 155 | Hits 10

As most of you know that latest update from Microsoft about SQL Server 2016 was released of CTP2.1 version. There is a long list of new features getting introduced in SQL Server 2016. In this blog, I ...

SQL SERVER Tips working with Files inside SQL Server

Journey to SQL Authority with Pinal Dave, June 26, 2015
Views: 131 | Hits 12

When two SQL enthusiasts meet, often the conversation drifts to questioning each others understanding and implementation. Now, when two DBAs meet it is always a great to see how the conversation goes ...

How to Get NULLs Horribly Wrong in SQL Server

Simple Talk, June 25, 2015
Views: 86 | Hits 10

NULLs in SQL are a mixed blessing.The Three-Valued Logic of SQL has its uses but can cause difficulties to anyone who assumes that NULL has a 'value'. It can make reports go horribly wrong in a number...

SQL SERVER How Do We Find Deadlocks? Notes from the Field #086

Journey to SQL Authority with Pinal Dave, June 25, 2015
Views: 117 | Hits 4

[Note from Pinal]: This is an 86thepisode of Notes from the Fields series.Deadlock is very annoying when they happen in our database. In my early career my biggest frustration was I had no idea why th...

SQL SERVER FIX Export Error Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Journey to SQL Authority with Pinal Dave, June 24, 2015
Views: 118 | Hits 2

Working with Excel files can always be of great interest. I have not seen a reporting solution in the world that doesnt give an option to export to excel. If you are talking about two separate solutio...

SQL SERVER PowerShell to Count Number of VLFs in SQL Server

Journey to SQL Authority with Pinal Dave, June 23, 2015
Views: 105 | Hits

If you are a seasoned DBA, then the conversations and monitoring VLFs is something you are already doing. To learn about basics of VLF, check my earlier blog at: SQL SERVER Detect Virtual Log Files (...

SQL SERVER Evaluation Period Has Expired How to Activate SQL Server?

Journey to SQL Authority with Pinal Dave, June 22, 2015
Views: 120 | Hits 1

Sometimes we are in a hurry that we forget activities that are pending. A typical example would be like walking into a room and we keep thinking about why we were there in the first place. Isnt that c...

Interview Question of the Week #025 How to Implement Paging in SQL Server?

Journey to SQL Authority with Pinal Dave, June 21, 2015
Views: 134 | Hits 7

Here is a very popular question when SQL Server interview consists of live coding example. Question: How to implement paging in SQL Server? Explain with the help of script. Answer: Here is the script ...