Feeds:
Posts
Comments

Archive for the ‘SQL Utility’ Category

SQL SERVER – Three Efficiency Tools for SQL Server from Devart

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on April 1, 2013 | Leave a Comment »

I just returned from successful road trip of TechEd India. The trip was extremely successful and I have got big chance to engage with community and friends. One of the most frequently asked question during the trip was what kind of efficiency tools do I use while working with SQL Server. I use many different tools and here is the list of my most favorite tools from Devart. If you are using them, do let me know as I would like to get your feedback about the tools.

SQL SERVER – Auto Complete and Format T-SQL Code – Devart SQL Complete

Some people call it laziness, some will call it efficiency, some think it is the right thing to do. At any rate, tools are meant to make a job easier, and I like to use various tools. Wanting a more efficient way to do something is not inherently lazy.  That’s how I see any efficiency tools. A few days ago I found Devart SQL Complete.  It took less than a minute to install, and after installation it just worked without needing any tweaking.  Once I started using it I was impressed with how fast it formats SQL code – you can write down any terms or even copy and paste.  The worst thing to encounter is code that goes all the way to the right side, and you have to scroll a million times because there are no breaks or indents.  SQL Complete will take care of this for you – if a developer is too lazy for proper formatting, then Devart’s SQL formatter tool will make them better, not lazier.

SQL SERVER – Development Productivity Tool – dbForge Studio for SQL Server

I have talked a little bit about dbForge before, but I would like to do a full product review now. Devart’s dbForge Studio for SQL Server is a fantastic tool for SQL Server development, as soon as I installed it I found so many things to learn. First off, it will increase SQL coding almost instantly. There is very little to learn, you are not just memorizing codes to “cheat” off of. DbForge Studio provides code completion options and automatic SQL formatting, so that you know your code will work. One of my favorite feature is “snippets,” which stores parts of code that you use over and over to cut down on typing and searching – because you know they’re always a few commands you use again and again!

SQL SERVER – Autocomplete and Code Formatting Tool – SQL in Sixty Seconds #041 – Video

Developers are often very busy in writing code and building application rapidly while racing against strict deadline. Developers want a tool which can help them aid them to write faster code along with a tool which auto format the code, so when they visit the code again they can read it. I came across a similar tool which can help achieve both the goals.

You can download the Devart SQL Complete demonstrated in this video for free.

Let us see the same concept in following SQL in Sixty Seconds Video:

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

About these ads

Read Full Post »

SQLAuthority News – SQL Server Data Tools – Business Intelligence for Visual Studio 2012 – SQL Server 2012 Data-Tier Application Framework

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on March 10, 2013 | 3 Comments »

Microsoft SQL Server Data Tools provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform within Visual Studio.  The SQL Server Object Explorer in Visual Studio offers a view of your database objects similar to SQL Server Management Studio. SQL Server Object Explorer allows you to do light-duty database administration and design work. You can easily create, edit, rename and delete tables, stored procedures, types, and functions. You can also edit table data, compare schemas, or execute queries by using contextual menus right from the SQL Server Object Explorer. Database developers can use the SQL Server Object Explorer in VS to easily create or edit database objects and data, or execute queries. The Microsoft SQL Server 2012 Data-Tier Application Framework (DACFx) is a component which provides application lifecycle services for database development and management for Microsoft SQL Server and Windows Azure SQL Databases.

Download SQL Server Data Tools – Business Intelligence for Visual Studio 2012

Download SQL Server 2012 Data-Tier Application Framework

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

Read Full Post »

SQL SERVER – Development Productivity Tool – dbForge Studio for SQL Server

Posted in PostADay, SQL, SQL Authority, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on February 14, 2013 | 2 Comments »

I have talked a little bit about dbForge before, but I would like to do a full product review now. Devart’s dbForge Studio for SQL Server is a fantastic tool for SQL Server development, as soon as I installed it I found so many things to learn.

First off, it will increase SQL coding almost instantly. There is very little to learn, you are not just memorizing codes to “cheat” off of. DbForge Studio provides code completion options and automatic SQL formatting, so that you know your code will work. One of my favorite feature is “snippets,” which stores parts of code that you use over and over to cut down on typing and searching – because you know there always a few commands you use again and again! Another time saver is the hints option, which will show you information about objects, and the navigation tool that allows toggling between items using only the F12 key.

Of course, all these features would simply be interesting trivia if you couldn’t design and alter tables, too. But dbForge Studio has that covered as well. Tables are always the hardest part, but with Table Designer, you can quickly set the properties using the visual table editors, then edit the script and rebuild as needed, previewing changes before going live. It really takes a lot of the stress out of altering tables.

Changing your database and the information stored there can also be a stress-filled event. But with Database Diagram tool, you can also edit and manipulate your database quickly and preview changes.  Scaling, grouping, and printing are no problem with Database Diagram. You can also build virtual connections to help visualize your data.

Of course, coding and modifications can seem simple in comparison to tackling the data within the database, especially when there is a complex structure and multiple dependencies. With Schema Compare, you can synchronize your database, even when they are extremely complex. You will also be alerted if there are problems so you can solve problems early, before they become catastrophes.  Schema Compare takes “snap shots” of your SQL Server database for easy comparisons later, which heads off drift. And if you are thinking that this is too good to be true, Schema Compare also has deployment script that help target different SQL Server editions, so it doesn’t matter what version you use, you can still use this tool.

DbForge Studio also allows automating deployment of the database to the production server. There are multiple tools available to help with this, including T-SQL Debugger, Query Profiler, Query Builder, and Data Import & Export. All of these tools can be scheduled and automated, and use easy “point and click” options rather than tedious coding.

While all this automating sounds great, anyone who has had to create and deal with reports is probably wondering if this can be automated, as well. The answer is yes! The Data Report Designer will compile your data and create a report in any of nine formats, so you have the perfect feedback. You can also schedule automatic report delivery.

And last, but certainly not least, let’s deal with the security features in dbForge Studio . I cannot say enough about how important security is, and how well Devart’s tool deals with this subject. The Security Manager feature allows the user to create visual editors for users, log-ins, and roles, and provides a feature for batch editing of objects. Of course, one of the most difficult tasks in security is fine-tuning the security to each individual user. You probably see where I am heading with this – the Security Manager allows setting up different privileges per schema object.

I hope this quick review has convinced some DBAs (and non DBAs!) to give dbForge Studio for SQL Server a try. I am sure you will not be disappointed. I certainly was not! Check out – dbForge Studio for SQL Server.

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

Read Full Post »

SQL SERVER – How to Hide Yourself from SQL Server? – Guest Post by Balmukund Lakhani

Posted in Database, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on January 9, 2013 | 5 Comments »

Balmukund Lakhani (Blog | Twitter | Site) is currently working as Technical Lead in SQL Support team with Microsoft India GTSC. In past 7+ years with Microsoft he was also a part of the Premier Field Engineering Team for 18 months. During that time he was a part of rapid on-site support (ROSS) team. Prior to joining Microsoft in 2005, he worked as SQL developer, SQL DBA and also got a chance to wear his other hat as an ERP Consultant.

Balmukund is a great friend and one of the finest SQL Server Expert I know. When I requested him for Guest Post, he has indeed come up with a fantastic blog post and very interesting title of the blog post. I am sure many wants to learn the trick to hide from SQL Server, so here it goes in his own words.

Important: It is Balmukund’s Birthday Today – please wish him all the best for exciting SQL filled New Year! Wish him on Twitter or leave a comment on his Blog.

I am sure that all of us have used SQL Server Management Studio countless times. It’s the common tool used by all DBA and developers. Most of the time we give server name, user name and password (if needed) and hit connect. Right? Have you ever clicked on “Options >>” button and made some changes in connection properties? Pinal wrote about color coding of servers, using “use custom color” option, here and posted a video here which is available in connection properties tab.

In this blog, we are going to talk about the third tab “Additional Connections Parameters”. When we want to test connecting string and those options are not available in earlier two tabs, then we can provide them in this tab (available in SQL 2008 onwards). The parameters should be separated by semicolons (;). There are a few parameters in connection string which can be spoofed. Let us start from hiding from SQL Server.

Attempt 1: Hide from SQL Server

Enter following parameters in the Additional Connection Parameter and click on Connect.
Application Name=Pinal-SSMS;Workstation ID=Pinal-PC

Once we connect and try to check our connection properties, the result of this would be as follows:

SELECT HOST_NAME, program_name
FROM sys.dm_exec_sessions
WHERE session_id = @@spid

If any DBA sees this, they would think that Pinal is working but it’s Balmukund in reality. And, I have hidden myself.

Attempt 2: Hide SQL Server from User (Deceive User)

Now we have hidden ourselves from SQL Server – let us do something crazy. Let us hide the SQL Server from the user. This can be extremely confusing so I suggest to use it carefully or never use it at all. If I can, I would have put red flashing lights around previous statement.

Now let us change the Data Source property in Additional Connection Parameters place. Let us confuse user that they are connecting to instance SQLServer2000 even though they are really connecting to instance BIG. My localhost is named as BIG and in following connection I am providing a incorrect server name Big\SQLServer2000 in the Server Name. In normal case it will give us the error. However, in this case we will provide an incorrect name in the Login >> ServerName connection.

We will right away click on ”Additional Connection Parameters” and will enter following text in the string Parameters:  Data Source=localhost

Now let us hit connect and you will see following situation.

Well, as warned earlier, SSMS would override the value and hence we are connected to a server which is in additional connection parameters. Everywhere in SSMS, we would see instance SQLServer2000 which is in login tab, but actual server is the SQL Server 2012. Now imagine a drop database executed in SSMS, thinking that this was ainstance SQLServer2000 but they end up on instance BIG. It is quite possible some of the commands will now return error if they are deprecated in instance SQLServer2000. This will be fun prank – which you should not play with anyone.

Moral of the story: Be careful while using this tab. Use this only for those settings which are not available in management studio by default.

Hope you have learned something new today.

Thanks Balmukund Twitter for this wonderful guest post.

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

Read Full Post »

SQL SERVER – Auto Complete and Format T-SQL Code – Devart SQL Complete

Posted in PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology on December 17, 2012 | 9 Comments »

Some people call it laziness, some will call it efficiency, some think it is the right thing to do. At any rate, tools are meant to make a job easier, and I like to use various tools. If we consider the history of the world, if we all wanted to keep traditional practices, we would have never invented the wheel.  But as time progressed, people wanted convenience and efficiency, which then led to laziness. Wanting a more efficient way to do something is not inherently lazy.  That’s how I see any efficiency tools.

A few days ago I found Devart SQL Complete.  It took less than a minute to install, and after installation it just worked without needing any tweaking.  Once I started using it I was impressed with how fast it formats SQL code – you can write down any terms or even copy and paste.  You can start typing right away, and it will complete keywords, object names, and fragmentations. It completes statement expressions.  How many times do we write insert, update, delete?  Take this example: to alter a stored procedure name, we don’t remember the code written in it, you have to write it over again, or go back to SQL Server Studio Manager to create and alter which is very difficult.  With SQL Complete , you can write “alter stored procedure,” and it will finish it for you, and you can modify as needed.

I love to write code, and I love well-written code.  When I am working with clients, and I find people whose code have not been written properly, I feel a little uncomfortable.  It is difficult to deal with code that is in the wrong case, with no line breaks, no white spaces, improper indents, and no text wrapping.  The worst thing to encounter is code that goes all the way to the right side, and you have to scroll a million times because there are no breaks or indents.  SQL Complete will take care of this for you – if a developer is too lazy for proper formatting, then Devart’s SQL formatter tool will make them better, not lazier.

SQL Management Studio gives information about your code when you hover your mouse over it, however SQL Complete goes further in it, going into the work table, and the current rate idea, too. It gives you more information about the parameters; and last but not least, it will just take you to the help file of code navigation.  It will open object explorer in a document viewer.  You can start going through the various properties of your code – a very important thing to do.

Here are are interesting Intellisense examples:

1) We are often very lazy to expand *however, when we are using SQL Complete we can just mouse over the * and it will give us all the the column names and we can select the appropriate columns.

2) We can put the cursor after * and it will give us option to expand it to all the column names by pressing the Tab key.

3) Here is one more Intellisense feature I really liked it. I always alias my tables and I always select the alias with special logic. When I was using SQL Complete I selected just a tablename (without schema name) and…(just like below image)

… and it autocompleted the schema and alias name (the way I needed it).

I believe using SQL Complete we can work faster.  It supports all versions of SQL Server, and works SQL formatting.  Many businesses perform code review and have code standards, so why not use an efficiency tool on everyone’s computer and make sure the code is written correctly from the first time?  If you’re interested in this tool, there are free editions available.  If you like it, you can buy it.  I bought it because it works.  I love it, and I want to hear all your opinions on it, too.

You can get the product for FREE. 

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

Read Full Post »

Older Posts »