SQL's Rounding Anomaly!

#roundinginsql Feb 10, 2018

If you too have used round function during coding somewhere, I am sure you must have scratched your head when you first encountered it.


Let's get straight to the business. So we have the requirement to display the result of a calculation to 2 nearest decimal places. For example 10/3 = 3.33

Now the first thing we need to do is to multiply the numerator with 1.0 to get the result in decimals. Why we do it? Well, that topic itself deserves some research to it.

Coming back to Round function you would have thought that Round(10 * 1.0 / 3, 2) would have done the job. However, not quite, we get the following result 

So I discovered 3 ways to get what we want:-

1. Using Cast and Decimal

 

2. Using Cast and Numeric

3. Using Format 
Introduced in version 2012 and according to me the best method

 

I think if there is a front-end application which feeds off from this calculations' value than we always have the luxury to get the desired output using front-end code. But...

Continue Reading...

Fix Corrupted Database With Stellar Pheonix SQL Database Repair

#repairdatabase #stellar Feb 03, 2018

Automation is the need of the hour. Many companies are striving hard to come with the products that would ease how things are done or things would have been done if we take the traditional inbuilt route of solving issues. 

I came across one such Product/Tool built by Steller. The product is given the name Steller Phoenix SQL Database Repair.
So if you have a corrupted database file (MDF), stellar can come to your rescue. This is all the more important in a DATA DRIVEN WORLD where losing data is a crime. For me, the standout features of this tool are:-
1. Ease of Use in terms of GUI and presentation layer.
2. Attaching or creating the database in SSMS (SQL Server Managment Studio) directly and not just repairing the corrupted file.
3. Saving Time and Money (trust me this is a no-brainer)

Here is how the tool works:-

1. Launch it from desktop shortcut

2. Select the corrupted mdf file

3. Click on Repair

4. Confirm the process went smooth

5. Saving the repaired file

6....

Continue Reading...

SQL QUERIES BEING ASKED IN INTERVIEWS

#sqlinterview #sqlqueries Jan 26, 2018

It is important to prepare yourself beforehand with some of the basic queries that are being asked in most of the interviews. This is also important because, during the interview, you sometime could get completely blackout because of the nervousness or excitement even though you know the answer or the logic to write that particular query.

Listing some of the common questions which I have encountered in some of the interviews:-

1. Eliminate duplicate from a SQL table

Simulation Script:-


CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

Delete Duplicate Script:-


;WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

2.  Obtain...

Continue Reading...

BEAUTIFY YOUR SQL CODE : SQL PRETTY PRINTER

#sqlformat Jan 16, 2018

Writing code neatly is considered a "good manner" when it comes to readability or sharing your code with other developers. 

If you are someone like me who does not believe in indentation and writes code randomly and not that eye-catchy, you will probably need to download this plug-in. I have been using it for a while and it works like charm.

Even though you have the in-house capability in SSMS 2017, its worth giving this plug-in a try because of the additional benefits such as producing clean SQL for the clients, maintaining coding standards, etc

Link to download SQL Pretty Printer

 

Continue Reading...

SQL Query Performance Improving Trick : SQL Query Running Forever

#sqlqueryfinetuning Jan 05, 2018

For starters, so relieved to fine-tune a SQL query which was bothering me for an hours time. 

When it comes to increasing the performance of any query, we most of the times, if not always, have to look at the actual execution plan. But what if the query never ends stop executing and runs forever. 

I would like to share a trick which I use in these situations. I basically query the top 100 records or more/less based on the underlining data volume to get the feel of what execution plan is doing. 

Something like:- SELECT TOP 100 Columns,......

By studying the execution I deploy certain changes and it seems to work in the majority of the occasions. 

Like to know what techniques you follow in order to find out the root cause of an issue if THE SQL QUERY RUNS FOREVER?

 

 

Continue Reading...

Whats new in SQL Server 2017?

 

As we wrap up 2017, it is time to look back and discuss what Microsoft introduced in SQL Server 2017.

I think the standout game changer feature is the ability to incorporate Python into SQL Server. 

Refer SQL Server 2017 Features for the complete list and comprehensive details.

Synopsis of New Features in SQL Server 2017

  1. Database 
  •  Automatic Database Tuning
  • Select INTO (ON)
  • String Functions example CONCAT_WS, Translate and Trim
  • Bulk Access for CSV and AZURE BLOB files

    2. Integration Services

  • Scale-out for SSIS: Run SSIS on multiple machines, Use 32bitruntime property
  • Run SSIS Packages on LINUX

    3. Analysis Services

  • GETDATA Sources
  • 1400 Compatibility Level

    4. Reporting Services

  • Supports Comments and Attachments

     

 

Continue Reading...

SSIS Error Inserting Data from Excel 2016 to SQL 2005 : "The value violated the integrity constraints for the column."

#database #excel #sql #ssis Dec 22, 2017

Ran into an interesting issue today. Looks pretty basic but yet you can avoid it by knowing the solution in the first case. Here it is

Symptom: When you try to insert data from an excel to SQL table through SSIS, the process errors out. 

"The value violated the integrity constraints for the column."

In our scenario, we had an excel sheet which consisted of deleted and blank rows. These are identified as NULLs in SSIS.

So when you have the target column in SQL table defined as NOT NULL, the process fails.

Solution: We have 2 main solutions for this.

  1. First is delete these empty rows from the excel sheet itself.

     2. Change the target column to allow NULL values 

i.e. ALTER TABLE TargetTable ALTER COLUMN TargetColumn INT NULL

 

Here you go,  a short and sweet solution to a short and easy problem.

Don't forget to check out SQL4ALL and to download free pdf consisting of important Interview Questions.

 

Lokesh -...

Continue Reading...
Close

50% Complete

Two Step

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.