Feeds:
Posts
Comments

Archive for the ‘Technology’ Category

SQL SERVER – Weekly Series – Memory Lane – #029

Posted in Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 18, 2013 | Leave a Comment »

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

List all the database
A Simple script which list all the database from the server.

Function to Parse AlphaNumeric Characters from String
Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is a very handy function when working with Alphanumeric String only. I have used this many times.

Query to Find First and Last Day of Current Month
The blog contains the query, which will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

Binary Sequence Generator – Truth Table Generator
Run script from the blog in the query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively.

Disable Index – Enable Index – ALTER Index
A straight to script blog where I demonstrate how to enable and disable index.

2008

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2
There is lots of confusing going on about difference between TempTable and TempVariable. In this blog I take another attempt about how to find difference between TempTable and TempVariable.

SQL SERVER Database Administrator Job Description
I have received quite a lot of requests to update it or post something similar. Writing SQL Articles are easier than writing Job description for DBA. I have read many job description and job posting at Best SQL Jobs and found the following job description.

2009

This year, I have attended my very first TechEd and presented very first session ever. Here is a photo with my wife Nupur at this event.

How to Drop Temp Table – Check Existence of Temp Table
A quick direct to script blog post.

2010

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
The difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD along with the disabled clustered index. Instead of writing a big theory, we will go over the demo right away. Here are the steps that we intend to follow.

1) Create Clustered and Non-clustered Index
2) Disable Clustered and Non-clustered Index
3) Enable – a) All Indexes, b) Clustered Index

Find Most Expensive Queries Using DMV
I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online.

List All the DMV and DMF on Server
“How many DMVs and DVFs are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Trainings. The answer is very simple in this blog post.

2011

Import CSV File into Database Table Using SSIS
It is a very frequent request to upload CSV file to database or Import CSV file into a database. I have previously written an article how one can do this using T-SQL over here  Import CSV File Into SQL Server Using Bulk Insert. One of the request which I quite often encounter is how I can do the same using SSIS package and automate it. Today we will understand the same using images.

Restart Remote Computer – Shutdown Remote Computer
Here is a quick trick which I use almost everyday. I have more than one computer at my desk and I manage multiple instances from a single monitor. Some of my computers are in a different location and I have to physically walk there. In this blog post, I demonstrated a quick method where I can use CMD prompt to restart and shutdown remote computer.

2012

Columnstore Index and sys.dm_db_index_usage_stats
Regular SQL Server DMV does not give information for usage stats for columnstore indexes and for that one has to use different DMV. In this blog post I explain the same in detail with DMV script. I also describe my journey how I come across this DMV.

Get Schema Name from Object ID using OBJECT_SCHEMA_NAME
Sometime a simple solution have even simpler solutions but we often do not practice it as we do not see the value in it or find it useful. Well, today’s blog post is also about something which I have seen not practiced much in codes. We are so much comfortable with an alternative usage that we do not feel like switching how we query the data.

SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series
Here are the five videos which Rick and I have built for Joes 2 Pros series.

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

About these ads

Read Full Post »

SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters

Posted in PostADay, SQL, SQL Authority, SQL Function, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 17, 2013 | Leave a Comment »

Earlier this week, I asked a puzzle about how REPLICATE works with 8000 and over 8000 characters. I strongly suggest to read the original blog post where I have described the problem in detail SQL SERVER Puzzle – REPLICATE over 8000 Characters.

Just quick to summarize the puzzle. Here is the quick recap of the same.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Answer 1:

The reason for the same is that if the first parameter of the REPLICATE function is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first parameter must be explicitly cast to the appropriate varchar(max) or nvarchar(max).

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

Answer 2:

To return the result as 11000, one has to just CAST or CONVERT the first parameters to VARCHAR(MAX) or NVARCHAR(MAX). Here is the example of the same.

DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),'C'),11000)
SELECT LEN(@ThirdString) LenThirdString;

Now let us see the result set.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

If you notice that this was not very difficult puzzle but it was interesting for sure. There are so many valid answers that it will be not possible to name every single person. I strongly encourage all of you to go over the original blog post and read all the comments. Though all the comments are very similar there are so many new information there that I will say wealth of information just right there in the comments area.

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

Read Full Post »

SQLAuthority News – 2500 Blog Posts, 2 Million Views per month, 62 Million Total Views

Posted in About Me, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, tagged on May 16, 2013 | 99 Comments »

Today, I am very happy as the journey I started almost 6.5 years ago has very important milestone. I have stopped blogging about my milestone for a long time as I believe that was just taking up the space in my blog and was not providing any useful information. However, today is a special day. This is my 2500th blog post and now the next 2500th blog post will come after many years.

When I started this blog I had no idea that I will be blogging after more than 6 years and I will ever reach to 2500th blog post. It was all just started as my personal bookmark. I was expecting that I will be a single reader of this blog and had no idea for a long time that even other people can read my blog as well. Once the very first comment appeared everything was just new world for me.

I never thought I would be able to engage with so many people from all over the world. I initially thought my blog will be majorly read by Indians as India has largest population but I am delighted to see that there is a balance among continents. As of today, this blog is the most read in USA and second place is India. Thank you readers from all over the world to support me  in spreading the knowledge.

Click to Enlarge

Another interesting and important detail is that I have been receiving over 2,000,000 views per month consistently every month. I never thought so many IT Professional would be reading this blog. When I see this 2 million number, I wonder often about readers.

If you are my blog reader, I would like to know you, would you please tell me who you are and why do you visit this blog? I encourage all you to leave a comment in the comments section. On this 2500th blog post, I will select random 10 readers and will send them surprise educational gift.

Online Course on how to Build a Successful Blog

I am often asked what are the best practices to build a successful blog. I have built a video course on the topic of How to build a successful blog. 

Watch the teaser of this course.

Here are few of the topics which I am covering in the course:

  • Blogging – Concepts, Ideas And Motives
  • Getting Started With Blogging
  • Writing An Interesting Blog
  • Blogging Rules, Ethics And Etiquette
  • Frequently Asked Questions
  • Checklist For Building Successful Blog

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

Read Full Post »

SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology, tagged on May 15, 2013 | 6 Comments »

We all love puzzles and here is interesting puzzle which you can play with me and win Amazon Gift Cards and Bubble Copter R/C. The contest for Amazon Gift Card is open worldwide, however, Bubble Copter winner will be chosen from USA only.

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

Question: Why do Query 1 return results but Query 2 does not return any result?

Answer this question in comments area along with the question in Step2.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.

Giveaway:

  • 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 250)
  • One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

Rules and Conditions:

  • Contest open till May 25 12:00 GMT. 
  • Please leave your answer in the comment area in following format:
    • Answer to Q1:
    • Answer to Q2:
  • Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
  • The answers will be kept hidden till the winner will be announced to have fair competition.

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

Read Full Post »

SQL SERVER – How to use xp_sscanf in Real World Scenario?

Posted in PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 14, 2013 | 6 Comments »

I need your help. I recently came across extended stored procedure xp_sscanf. After reading a lot about it and searching online, I could not figure out how and where in real world, I will use this function.

Microsoft documentations suggest that this extended stored procedure reads data from the string into the argument locations specified by each format argument. I still do not get it. I know it is very similar to C function but again I am not sure where in the real world I will use this function. Here is the demonstration of how this function works.

Following example is an enhanced version of the example provided on MSDN.

DECLARE @valueb VARCHAR(20), @filename VARCHAR(20), @message VARCHAR(20)
EXEC xp_sscanf 'sync -b123 -fauthors10.tmp -rrandom', 'sync -b%s -f%s -r%s',
@valueb OUTPUT, @filename OUTPUT, @message OUTPUT
SELECT @valueb, @filename, @message

The above query will return us following result:

-------------------- -------------------- --------------------
123                  authors10.tmp        random

Here is the result set.

You can see how xp_sscanf retrieves the parameters from the string and returns as an output. However, I am still not sure where I will use this feature in real world scenarios. Any insight and or guidance will be helpful.

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

Read Full Post »

Older Posts »