Search Results

Search found 2465 results on 99 pages for 'dave hillier'.

Page 7/99 | < Previous Page | 3 4 5 6 7 8 9 10 11 12 13 14  | Next Page >

• SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video

  - by pinaldave

  Today’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Misconception and Resolution. Quite often I have seen people getting confused with certain behavior of the T-SQL. They expect SQL to behave certain way and SQL Server behave differently. This kind of issue often creates confusion and frustration. Sometime I have seen them also confusing it with bug and submitting the bug, where reality is totally different. Similar concept which are going to see today. I have seen quite commonly developer assuming that table various will be rolled back when transaction is rolled back. This sixty seconds video describes that table various are not rolled back when transactions are rolled back. More on Errors: Difference Temp Table and Table Variable – Effect of Transaction Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31 I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Video

  Read the article

• SQL SERVER – Simple Installation of Master Data Services (MDS) and Sample Packages – Very Easy

  - by pinaldave

  I twitted recently about: ‘Installing #sql Server 2008 R2 – Master Data Services. Painless.‘ After doing so, I got quite a few emails from other users as to why I thought it was painless. The reason was very simple- I was able to install it rather quickly on my laptop without any issues. There were a few requests along with these emails sent to me, which regards to how to install MDS, as well sample databases. Please note that I am the admin of my machine and I installed this MDS as the admin as well. Talk to your network administrator to figure out the best suitable settings for better security of login users. Additionally, since MDS is only supported on a 64-bit machine, I had rebuilt my computer a week before with a 64-bit OS and 64-bit SQL Server to go with it. Here is a quick picture tour of the installation. First of all, go to your SQL Server 2008. Install self-extracted folder and find the .msi file for C:\1033_enu_lp\x64\setup\masterdataservices.msi. Once you clicked on the file, follow the image tour below. You can ask me any questions in case you are still confused with any of the steps of the installation. While searching the internet for a similar installation process, I have landed on the official blog of MDS team where they have many interesting posts about it. If any concept written on my posts are contradictory to the information on the official blog, I suggest that you should follow the advice of official blog. Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers

  - by pinaldave

  There are plenty of the things life one can make it simple. I really believe in the same. I was yesterday traveling for community related activity. On airport while returning I met a SQL Enthusiast. He asked me if there is any simple way to find maximum between two numbers in the SQL Server. I asked him back that what he really mean by Simple Way and requested him to demonstrate his code for finding maximum between two numbers. Here is his code: DECLARE @Value1 DECIMAL(5,2) = 9.22 DECLARE @Value2 DECIMAL(5,2) = 8.34 SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 - @Value2))) AS MaxColumn GO I thought his logic was accurate but the same script can be written another way. I quickly wrote following code for him and which worked just fine for him. Here is my code: DECLARE @Value1 DECIMAL(5,2) = 9.22 DECLARE @Value2 DECIMAL(5,2) = 8.34 SELECT CASE WHEN @Value1 > @Value2 THEN @Value1 ELSE @Value2 END AS MaxColumn GO He agreed that my code is much simpler but as per him there is some problem with my code which apparently he does not remember at this time. There are cases when his code will give accurate values and my code will not. I think his comment has value but both of us for the moment could not come up with any valid reason. Do you think any scenario where his code will work and my suggested code will not work? Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Two Puzzles – Answer and Win USD 25 Gift Card

  - by pinaldave

  Today I have two simple T-SQL Puzzle. You can answer them and win USD 25 Gift card. The gift card will be sent in email to winner. You will get choice of Gift Card brand based on your preference and country location. Puzzle 1: What will be the outcome and why? DECLARE @x REAL; SET @x = 9E-40 SELECT @x; The outcome here is obvious as I have used negative number in assignment. What is the reason behind the same? Puzzle 2: Why will be the outcome different from Puzzle 1: DECLARE @y REAL; SET @y = 9E+40 SELECT @y; The outcome of this puzzle very different from puzzle 1  as I have used positive number. There is number six (6) in the resultset why? Msg 232, Level 16, State 2, Line 2 Arithmetic overflow error for type real, value = 90000000000000006000000000000000000000000.000000. How to participate To win the Gift Card USD 25 you will have to answer both of the question on my Facebook page. If you are on twitter – you can increase the chance of winning by tweeting your participation. This contest is open for any one from any country. The winner will be selected Randomly. Winner will be announced on July 7, 2011. Related Post: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

  Read the article

• SQL SERVER – 2011 – Multi-Monitor SSMS Windows

  - by pinaldave

  I have a dual screen arrangement at my home system. I love it because it’s very convenient. When I am working with SQL Server 2008 R2 or any earlier versions, I would want to use both of the Monitor so I open two separate SQL Server Management Studio and work along with it. I have no complaints with my system, at all. I am totally fine with it. However, sometimes I face small issues, like when I just want a small code open in a separate window but I do not want the windows to take over the whole of another window. But then again, I am already used to this current system. Recently when I was working with SQL Server 2011 ‘Denali’ CTP1, I dragged one of the windows by accident, and suddenly it magically appeared out of its ‘Shell’ of SSMS and was appearing on a separate monitor. I played around a bit and figured out that SSMS now supports multi-monitor (or multi screen) support with single SSMS instance. We can now drag out and drag in any window and resize them at any size. Fantastic! If you are multi-monitor user, I am sure you will like this feature. This leads me to ask you question? Do you use multi-monitor system while working with SQL Server? Leave a quick comment. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQLAuthority News – Presenting at Virtual Tech Days TechEd Pre-Con – February 9, 2011

  - by pinaldave

  I will be presenting on following subject on Virtual Tech Days TechEd Pre-Con – February 9, 2011. Auditing Made Easy: Change Tracking and Change Data Capture Date and Time: February 9, 2011 11:45am-12:45pm Location: Online In this fast paced demo oriented session we will go over few of concept which are related to real life problem at customers. We often see developers and DBA looking for details like who has dropped the table, who has last modified any object as well what was actually modified. SQL Server 2008 has all the answers. It has various new methods for Auditing where not only you can know details about what was changed as well know who changed it as well. In addition to that we can capture way more details configuring Auditing. We can also work prevent changes if proper policy management is configured. If you have ever attended my session on this subject earlier, this is going to absolutely new session and very much demo oriented. There is going to be quiz at the end of the session and I promise that if you attend the session, you will get all the answers correct. Reference: Pinal Dave (http://blog.SQLAuthority.com)   Filed under: About Me, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology

  Read the article

• SQL SERVER – Difference between COUNT(DISTINCT) vs COUNT(ALL)

  - by pinaldave

  This blog post is written in response to the T-SQL Tuesday hosted by Jes Schultz Borland. Earlier today, I was presenting a 45-minute session at the Community College about “The Beginning SQL Server Database”. One of the students asked me the following question. What is the difference between COUNT(DISTINCT) vs COUNT(ALL)? I found this question from the student very interesting. He seems to have read the documentation (Book Online) and was then asking me this question. I always carry laptop which has SQL Server installed. I quickly opened it and ran the following script. After looking at the result, I think it was clear to everybody. Here is the script: SELECT COUNT([Title]) Value FROM [AdventureWorks].[Person].[Contact] GO SELECT COUNT(ALL [Title]) ALLValue FROM [AdventureWorks].[Person].[Contact] GO SELECT COUNT(DISTINCT [Title]) DistinctValue FROM [AdventureWorks].[Person].[Contact] GO The above script will give me the following results. You can clearly notice from the result set that COUNT (ALL ColumnName) is the same as COUNT(ColumnName). The reality is that the “ALL” is actually  the default option and it needs not to be specified. The ALL keyword includes all the non-NULL values. I know this is very simple and may be it does not change how we work; however looking at the whole angle, I really enjoyed the question. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology

  Read the article

• SQLAuthority News – SQL Server Interview Questions And Answers Book Summary

  - by pinaldave

  Today we are using computers for various activities, motor vehicles for traveling to places, and mobile phones for conversation. How many of us can claim the invention of micro-processor, a basic wheel, or the telegraph? Similarly, this book was not written overnight. The journey of this book goes many years back with many individuals to be thanked for. To begin with, we want to thank all those interviewers who reject interviewees by saying they need to know ‘the key things’ regardless of having high grades in class. The whole concept of interview questions and answers revolves around knowing those ‘key things’. The core concept of this book will continue to evolve over time. I am sure many of you will come along with us on this journey and submit your suggestions to us to make this book a key reference for anybody who wants to start with SQL Server. Today we want to acknowledge the fact that you will help us keep this book alive forever with the latest updates. We want to thank everyone who participates in this journey with us. Though each of these chapters are geared towards convenience we highly recommend reading each of the sections irrespective of the roles you might be doing since each of the sections have some interesting trivia about working with SQL Server. In the industry the role of accidental DBA’s (especially with SQL Server) is very common. Hence if you have performed the role of DBA for a short stint and want to brush-up your fundamentals then the upcoming sections will be a great review. Table Of Contents Database Concepts With Sql Server Common Generic Questions & Answers Common Developer Questions Common Tricky Questions Miscellaneous Questions On Sql Server 2008 Dba Skills Related Questions Data Warehousing Interview Questions & Answers General Best Practices [Amazon] | [Flipkart] Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Best Practices, Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology

  Read the article

• SQLAuthority News – Story of Seattle – SQLPASS 2011 Event Log

  - by pinaldave

  Just like every year I attended SQL PASS in Seattle earlier this month. The event was scheduled from Oct 11-14, 2011 in the convention center of the Seattle. I have been to Seattle more than 6 times so far so it is not a new city for me anymore. The city has always impressed me with its vibrant life and pleasant weather. Just like every other time, I had excellent experience once again in the city. Though I just arrived on the day of the event and left right after the event was over – I hardly visited Seattle – still some good experience to share. Here are few quick photographs from my quick trip of Seattle city. Skyline of Seattle Seattle Convention Center A Shop Tenzing Momo and Co at Pike St Market The Seattle Gum Wall Shoreline in Seattle Nigel and Paras First Starbucks (Relocated) People on Street of Seattle Food at Sandy’s – All Veg Well, this is a short summary of my extremely quick city tour of Seattle. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL PASS, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology

  Read the article

• SQL SERVER – Introduction to PERCENT_RANK() – Analytic Functions Introduced in SQL Server 2012

  - by pinaldave

  SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition. It will be very difficult to explain this in words so I’d like to attempt to explain its function through a brief example. Instead of creating a new table, I will be using the AdventureWorks sample database as most developers use that for experiment purposes. Now let’s have fun following query: USE AdventureWorks GO SELECT SalesOrderID, OrderQty, RANK() OVER(ORDER BY SalesOrderID) Rnk, PERCENT_RANK() OVER(ORDER BY SalesOrderID) AS PctDist FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY PctDist DESC GO The above query will give us the following result: Now let us understand the resultset. You will notice that I have also included the RANK() function along with this query. The reason to include RANK() function was as this query is infect uses RANK function and find the relative standing of the query. The formula to find PERCENT_RANK() is as following: PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1) If you want to read more about this function read here. Now let us attempt the same example with PARTITION BY clause USE AdventureWorks GO SELECT SalesOrderID, OrderQty, ProductID, RANK() OVER(PARTITION BY SalesOrderID ORDER BY ProductID ) Rnk, PERCENT_RANK() OVER(PARTITION BY SalesOrderID ORDER BY ProductID ) AS PctDist FROM Sales.SalesOrderDetail s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY PctDist DESC GO Now you will notice that the same logic is followed in follow result set. I have now quick question to you – how many of you know the logic/formula of PERCENT_RANK() before this blog post? Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – ORDER BY ColumnName vs ORDER BY ColumnNumber

  - by pinaldave

  I strongly favor ORDER BY ColumnName. I read one of the blog post where blogger compared the performance of the two SELECT statement and come to conclusion that ColumnNumber has no harm to use it. Let us understand the point made by first that there is no performance difference. Run following two scripts together: USE AdventureWorks GO -- ColumnName (Recommended) SELECT * FROM HumanResources.Department ORDER BY GroupName, Name GO -- ColumnNumber (Strongly Not Recommended) SELECT * FROM HumanResources.Department ORDER BY 3,2 GO If you look at the result and see the execution plan you will see that both of the query will take the same amount of the time. However, this was not the point of this blog post. It is not good enough to stop here. We need to understand the advantages and disadvantages of both the methods. Case 1: When Not Using * and Columns are Re-ordered USE AdventureWorks GO -- ColumnName (Recommended) SELECT GroupName, Name, ModifiedDate, DepartmentID FROM HumanResources.Department ORDER BY GroupName, Name GO -- ColumnNumber (Strongly Not Recommended) SELECT GroupName, Name, ModifiedDate, DepartmentID FROM HumanResources.Department ORDER BY 3,2 GO Case 2: When someone changes the schema of the table affecting column order I will let you recreate the example for the same. If your development server where your schema is different than the production server, if you use ColumnNumber, you will get different results on the production server. Summary: When you develop the query it may not be issue but as time passes by and new columns are added to the SELECT statement or original table is re-ordered if you have used ColumnNumber it may possible that your query will start giving you unexpected results and incorrect ORDER BY. One should note that the usage of ORDER BY ColumnName vs ORDER BY ColumnNumber should not be done based on performance but usability and scalability. It is always recommended to use proper ORDER BY clause with ColumnName to avoid any confusion. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQLAuthority News – Deployment guide for Microsoft SharePoint Foundation 2010

  - by pinaldave

  SharePoint and SQL Server both goes together – hands to hand. SharePoint installation is very interesting. At various organizations, the installation is very different and have various needs. SQL Server installation with SharePoint is equally important and I have often seen that it is being neglected. Microsoft has published the Deployment Guide for SharePoint Foundation. It talks about various database aspects as well. For optimal sharepoint installation the required version of SQL Server, including service packs and cumulative updates must be installed on the database server. The installation must include any additional features, such as SQL Analysis Services, and the appropriate SharePoint Foundation logins have to be added and configured. The database server must be hardened and, if it is required, databases must be created by the DBA. For more information, see: Hardware and software requirements (SharePoint Foundation 2010) Harden SQL Server for SharePoint environments (SharePoint Foundation 2010) Deploy by using DBA-created databases (SharePoint Foundation 2010) Deployment guide for Microsoft SharePoint Foundation 2010 Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: SharePoint

  Read the article

• SQL SERVER – First Month as DBA Trainee – Disasters and Recovery

  - by pinaldave

  This blog post is written in response to the T-SQL Tuesday hosted by Allen Kinsel. He has selected very interesting subject for T-SQL Tuesday – Disaster and Recovery. This subject took me in past – my past. There were various things, I had done or proposed when I started very first month as a DBA trainee. I was tagged along with very senior DBA in my organization who always protected me or correct my mistake. He was great guy and totally understand the young mind of over-enthusiastic Trainee DBA. I respect him very much. Here are few things which I had learned in my very first month (not necessarily I have practices them on production). Never compress (zip) native backup using any tools, when disaster happen sometime the extra time to un-compress the database can be too long and not acceptable for business SLA Do not truncate logs After restoring full database backup – only restore latest differential back, no need to restore all the backup Always write WHERE condition when deleting and updating Sr. DBA always advised me – always keep your résumé ready and car ready – you never know when you can not recover disaster! Well for sure it was a joke. Today’s T-SQL Tuesday remind me of my very first month as DBA trainee. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: About Me, Best Practices, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQLAuthority News – Presenting at Tech-Ed On Road – Ahmedabad – June 11, 2011 – Wait Types and Queues

  - by pinaldave

  I will be presenting in person on the subject SQL Server Wait Types and Queues at Ahmedabad on June 11, 2011. Here is the quick summary of the session. SQL Server Waits and Queues – Your Gateway to Perf. Troubleshooting Time: 11:15am – 12:15pm – June 11, 2011 Just like a horoscope, SQL Server Waits and Queues can reveal your past, explain your present and predict your future. SQL Server Performance Tuning uses the Waits and Queues as a proven method to identify the best opportunities to improve performance. A glance at Wait Types can tell where there is a bottleneck. Learn how to identify bottlenecks and potential resolutions in this fast paced, advanced performance tuning session. This session is based on my performance tuning Wait Types and Queues series. SQL SERVER – Summary of Month – Wait Type – Day 28 of 28 During the session there will be Quiz and those who gets right answer will get very interesting gifts from me. Do not miss a single minute of the event. We are also going to have two rock star speakers – Harish Vaidyanathan and Jacob Sebastian. Here is the details for the event: SQLAuthority News – Community Tech Days – TechEd on The Road – Ahmedabad – June 11, 2011 Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: About Me, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, T SQL, Technology

  Read the article

• SQLAuthority News – My Evaluation of Singapore SharePoint Conference

  - by pinaldave

  Earlier this year I presented at SQLAuthority News – Presenting at South East Asia SharePoint Conference – Oct 26, 27, 2010 – Singapore. I felt very good to be presenting at Singapore as SharePoint Conference as I was the only SQL Speaker at the event. The event was filled with SharePoint enthusiasts and lots of other experts from all around the globe. The event was one of the best organized event, I attended in subcontinent. I just received my feedback score of the event. I was very much surprised and stunned. My ratting are very high as well, my demo were considered as one of the best demo of the whole event. I am not sure how much feedback I can share with community as organizer did not specify to me but I am very certain that I am allowed to share my own feedback. Speaker – 4.39 (best score 4.74, average 3.84) Contents – 4.37 (best score 4.39, average 3.65) Demo – 4.48 (best score, 4.48, average 3.61) I am very glad that all of my efforts to do presentations at SharePoint Conference are finally paid up. I was very much worried earlier if attendees will accept me or not as I am coming as speaker from foreign technology and no one knows me there. I must thank all of you for the same. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SharePoint, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, T SQL, Technology Tagged: SharePoint

  Read the article

• SQL SERVER – Tell me What You Want to Listen – My 2 TechED 2011 Sessions

  - by pinaldave

  I am going to present two sessions at TechEd India on March 25th, 2011. I would like to know what do you want me to cover in this session. Watch the video taken by my wife when I was preparing for the session. Sessions Date: March 25, 2011 Understanding SQL Server Behavioral Pattern – SQL Server Extended Events Date and Time: March 25, 2011 12:00 PM to 01:00 PM SQL Server Waits and Queues – Your Gateway to Perf. Troubleshooting Date and Time: March 25, 2011 04:15 PM to 05:15 PM I promise following for both of my sessions: I will share the scripts demonstrated in the session right at the end of the sessions The sessions will be 300-400 level but I promise to make the concept very simple Less slides and lots of meaningful Demos Session close to real life cases and scenarios Surprise gifts to best participants I promise to answer all the questions either in session or right after the hall after the session Lots of Technical Education and FUN! Please leave your comments with your expectation and if you are going to attend the session do let me know here. We will for sure meet at the event and do some interesting talk. You can read the abstract of the session over here. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, T SQL, Technology Tagged: TechEd, TechEdIn

  Read the article

• SQL SERVER 2008 – 2011 – Declare and Assign Variable in Single Statement

  - by pinaldave

  Many of us are tend to overlook simple things even if we are capable of doing complex work. In SQL Server 2008, inline variable assignment is available. This feature exists from last 3 years, but I hardly see its utilization. One of the common arguments was that as the project migrated from the earlier version, the feature disappears. I totally accept this argument and acknowledge it. However, my point is that this new feature should be used in all the new coding – what is your opinion? The code which we used in SQL Server 2005 and the earlier version is as follows: DECLARE @iVariable INT, @vVariable VARCHAR(100), @dDateTime DATETIME SET @iVariable = 1 SET @vVariable = 'myvar' SET @dDateTime = GETDATE() SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT GO The same should be re-written as following: DECLARE @iVariable INT = 1, @vVariable VARCHAR(100) = 'myvar', @dDateTime DATETIME = GETDATE() SELECT @iVariable iVar, @vVariable vVar, @dDateTime dDT GO I have started to use this new method to assign variables as I personally find it very easy to read as well write. Do you still use the earlier method to declare and assign variables? If yes, is there any particular reason or just an old routine? I am interested to hear about this. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video

  - by pinaldave

  Today’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Speed up! – Parallel Processes and Unparalleled Performance. There are always special cases when it is about SQL Server. There are always few queries which gives optimal performance when they are executed on single processor and there are always queries which gives optimal performance when they are executed on multiple processors. I will be presenting the how to identify such queries as well what are the best practices related to the same. In this quick video I am going to demonstrate if the query is giving optimal performance when running on single CPU how one can restrict queries to single CPU by using hint OPTION (MAXDOP 1). More on Errors: Difference Temp Table and Table Variable – Effect of Transaction Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31 I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Video

  Read the article

• SQL SERVER – Puzzle to Win Print Book and Free 30 Days Online Training Material

  - by pinaldave

  Yesterday I had asked a simple question SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG with keeping two simple intention. We can all learn about new feature of SQL Server 2012 We can learn new feature of SQL Server 2012 while practicing on earlier version of SQL Server. While I was creating question due to copy-paste error the question was not correctly created. In simple word – I made a mistake. This created some confusion and I feel bad about this. Here is what we will do. Please read the question again and attempt to answer the question which I have asked in the blog post. Yesterday the give away was my SQL Server Interview Questions and Answers book. As the question was corrected after a while, the give away are now got sweeter. SQL Server Interview Questions and Answers book – 2 Copy 30 Days Online Training Material of Pluralsight. They have excellent learning resources – I have written my 6 hour learning experience over Learning SSAS (SQL Server Analysis Services) Online in 6 Hours. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Training, SQLServer, T SQL, Technology

  Read the article

• SQL SERVER – Mirroring Configured Without Domain – The server network address TCP://SQLServerName:50

  - by pinaldave

  Regular readers of my blog will be aware of my friend who called me few days ago with very a funny SQL Problem SQL SERVER – SSMS Query Command(s) completed successfully without ANY Results. This time, it did not take long before he called me up with another interesting problem, although the issue he was facing this time was not that interesting and also very specific to him, however, he insisted me to share with all of you. Let us understand his situation at first. My friend is preparing for DBA exam Exam 70-450: PRO: Designing, Optimizing and Maintaining a Database Server Infrastructure using Microsoft SQL Server 2008 and for the same, he was trying to set up replication on his local laptop. He had installed two different instances of SQL Server on his computer and every time when he started the mirroring, it failed with common error message. The server network address “TCP://SQLServer:5023? cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418) Well, before he contacted me, he searched online and checked my article written on the error in mirroring. However, he tried all the four suggestions, but it did not solve his problem. He called me at a reasonable time of late evening (unlike last time, which was midnight!). I even tried all the seven different suggestions myself, as previously proposed in my article; however, none of them worked. While looking at closely at services, I noticed something very simple. He was running all the instances on ‘Network Services’. In fact, his computer was a stand-alone computer. There was no network at all. Also, there was no domain or any other advance network concepts implemented. I just changed services from ‘Network Services’ to ‘Local System’ as his SQL Server was running on his local system and there were no network services. This prompted to restart the services. As this was not the production server and his development machine, we restarted the services on the laptop (do not restart services on production server without proper planning). After changing the ‘services log on’ account to localsystem, when he attempted to reconfigure the mirroring it worked right away. As usually in production server, proper domains are configured and advance network concepts are implemented I had never faced this type of problem earlier. My friend insisted to post this solution to his situation, wherein there was no domain configured and setting up mirroring was throwing an error. According to him, this is bound to help people, like him, who are preparing for certification using single system. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: SQL Certifications, SQL Mirroring

  Read the article

• SQL SERVER – Out of the Box – Activty and Performance Reports from SSSMS

  - by pinaldave

  SQL Server management Studio 2008 is wonderful tool and has many different features. Many times, an average user does not use them as they are not aware about these features. Today, we will learn one such feature. SSMS comes with many inbuilt performance and activity reports, but we do not use it to the full potential. Let us see how we can access these standard reports. Connect to SQL Server Node >> Right Click on it >> Go to Reports >> Click on Standard Reports >> Pick Any Report. Click to Enlarge You can see there are many reports, which an average users needs right away, are available there. Let me list all the reports available. Server Dashboard Configuration Changes History Schema Changes History Scheduler Health Memory Consumption Activity – All Blocking Transactions Activity – All Cursors Activity – All Sessions Activity – Top Sessions Activity – Dormant Sessions Activity -  Top Connections Top Transactions by Age Top Transactions by Blocked Transactions Count Top Transactions by Locks Count Performance – Batch Execution Statistics Performance – Object Execution Statistics Performance – Top Queries by Average CPU Time Performance – Top Queries by Average IO Performance – Top Queries by Total CPU Time Performance – Top Queries by Total IO Service Broker Statistics Transactions Log Shipping Status In fact, when you look at the above list, it is fairly clear that they are very thought out and commonly needed reports that are available in SQL Server 2008. Let us run a couple of reports and observe their result. Performance – Top Queries by Total CPU Time Click to Enlarge Memory Consumption Click to Enlarge There are options for custom reports as well, which we can configure. We will learn about them in some other post. Additionally, you can right click on the reports and export in Excel or PDF. I think this tool can really help those who are just looking for some quick details. Does any of you use this feature, or this feature has some limitations and You would like to see more features? Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – World Shapefile Download and Upload to Database – Spatial Database

  - by pinaldave

  During my recent, training I was asked by a student if I know a place where he can download spatial files for all the countries around the world, as well as if there is a way to upload shape files to a database. Here is a quick tutorial for it. VDS Technologies has all the spatial files for every location for free. You can download the spatial file from here. If you cannot find the spatial file you are looking for, please leave a comment here, and I will send you the necessary details. Unzip the file to a folder and it will have the following content. Then, download Shape2SQL tool from SharpGIS. This is one of the best tools available to convert shapefiles to SQL tables. Afterwards, run the .exe file. When the file is run for the first time, it will ask for the database properties. Provide your database details. Select the appropriate shape files and the tool will fill up the essential details automatically. If you do not want to create the index on the column, uncheck the box beside it. The screenshot below is simply explains the procedure. You also have to be careful regarding your data, whether that is GEOMETRY or GEOGRAPHY. In this example,  it is GEOMETRY data. Click “Upload to Database”. It will show you the uploading process. Once the shape file is uploaded, close the application and open SQL Server Management Studio (SSMS). Run the following code in SSMS Query Editor. USE Spatial GO SELECT * FROM dbo.world GO This will show the complete map of world after you click on Spatial Results in Spatial Tab. In Spatial Results Set, the Zoom feature is available. From the Select label column, choose the country name in order to show the country name overlaying the country borders. Let me know if this tutorial is helpful enough. I am planning to write a few more posts about this later. Note: Please note that the images displayed here do not reflect the original political boundaries. These data are pretty old and can probably draw incorrect maps as well. I have personally spotted several parts of the map where some countries are located a little bit inaccurately. Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Add-On, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Spatial, SQL Tips and Tricks, SQL Utility, T SQL, Technology

  Read the article

• SQL SERVER – Summary of Month – Wait Type – Day 28 of 28

  - by pinaldave

  I am glad to announce that the month of Wait Types and Queues very successful. I am glad that it was very well received and there was great amount of participation from community. I am fortunate to have some of the excellent comments throughout the series. I want to dedicate this series to all the guest blogger – Jonathan, Jacob, Glenn, and Feodor for their kindness to take a participation in this series. Here is the complete list of the blog posts in this series. I enjoyed writing the series and I plan to continue writing similar series. Please offer your opinion. SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28 SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait Type – Day 2 of 28 SQL SERVER – DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28 SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type – Day 4 of 28 SQL SERVER – Capturing Wait Types and Wait Stats Information at Interval – Wait Type – Day 5 of 28 SQL SERVER – CXPACKET – Parallelism – Usual Solution – Wait Type – Day 6 of 28 SQL SERVER – CXPACKET – Parallelism – Advanced Solution – Wait Type – Day 7 of 28 SQL SERVER – SOS_SCHEDULER_YIELD – Wait Type – Day 8 of 28 SQL SERVER – PAGEIOLATCH_DT, PAGEIOLATCH_EX, PAGEIOLATCH_KP, PAGEIOLATCH_SH, PAGEIOLATCH_UP – Wait Type – Day 9 of 28 SQL SERVER – IO_COMPLETION – Wait Type – Day 10 of 28 SQL SERVER – ASYNC_IO_COMPLETION – Wait Type – Day 11 of 28 SQL SERVER – PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_SH, PAGELATCH_UP – Wait Type – Day 12 of 28 SQL SERVER – FT_IFTS_SCHEDULER_IDLE_WAIT – Full Text – Wait Type – Day 13 of 28 SQL SERVER – BACKUPIO, BACKUPBUFFER – Wait Type – Day 14 of 28 SQL SERVER – LCK_M_XXX – Wait Type – Day 15 of 28 SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28 SQL SERVER – WRITELOG – Wait Type – Day 17 of 28 SQL SERVER – LOGBUFFER – Wait Type – Day 18 of 28 SQL SERVER – PREEMPTIVE and Non-PREEMPTIVE – Wait Type – Day 19 of 28 SQL SERVER – MSQL_XP – Wait Type – Day 20 of 28 SQL SERVER – Guest Posts – Feodor Georgiev – The Context of Our Database Environment – Going Beyond the Internal SQL Server Waits – Wait Type – Day 21 of 28 SQL SERVER – Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28 SQL SERVER – OLEDB – Link Server – Wait Type – Day 23 of 28 SQL SERVER – 2000 – DBCC SQLPERF(waitstats) – Wait Type – Day 24 of 28 SQL SERVER – 2011 – Wait Type – Day 25 of 28 SQL SERVER – Guest Post – Glenn Berry – Wait Type – Day 26 of 28 SQL SERVER – Best Reference – Wait Type – Day 27 of 28 SQL SERVER – Summary of Month – Wait Type – Day 28 of 28 Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Optimization, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL Wait Stats, SQL Wait Types, SQLServer, T SQL, Technology

  Read the article

• SQL SERVER – Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video

  - by pinaldave

  SQL in Sixty Seconds is indeed tremendous fun to do. Every week, we try to come up with some new learning which we can share in Sixty Seconds. In this busy world, we all have sixty seconds to learn something new – no matter how much busy we are. In this episode of the series, we talk about another interesting feature of SQL Server Management Studio. In SQL Server Management Studio (SSMS) we have two button side by side. 1) Execute (!) and 2) Debug (>). It is quite confusing to a few developers. The debug button which looks like a play button encourages developers to click on the same thinking it will execute the code. Also developer with a Visual Studio background often click it because of their habit. However, Debug button is not the same as Execute button. In most of the cases developers want to click on Execute to run the query but by mistake they click on Debug and it wastes their valuable time. It is very easy to fix this. If developers are not frequently using a debug feature in SQL Server they should hide it from the toolbar itself. This will reduce the chances to incorrectly click on the debug button greatly as well save lots of time for developer as invoking debug processes and turning it off takes a few extra moments. In this Sixty second video we will discuss how one can hide the debug button and avoid confusion regarding execution button. I personally use function key F5 to execute the T-SQL code so I do not face this problem that often. More on Removing Debug Button in SSMS: SQL SERVER – Read Only Files and SQL Server Management Studio (SSMS) SQL SERVER – Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video SQL SERVER – Discard Results After Query Execution – SSMS SQL SERVER – Tricks to Comment T-SQL in SSMS – SQL in Sixty Seconds #019 – Video SQL SERVER – Right Aligning Numerics in SQL Server Management Studio (SSMS) I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can. If we like your idea we promise to share with you educational material. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video

  Read the article

• SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell

  - by pinaldave

  Laerte Junior If you are a Powershell user, the name of the Laerte Junior is not a new name. He is the one man with exceptional knowledge of Powershell. He is not only very knowledgeable, but also very kind and eager to those in need. I have been attempting to setup Powershell for many days, but constantly facing issues. I was not able to get going with this tool. Finally, yesterday I sent email to Laerte in response to his comment posted here. Within 5 minutes, Laerte came online and helped me with the solution. He spend nearly 15 minutes working along with me to solve my problem with installation. And yes, he did resolve it remotely without looking at my screen – What a skilled and exceptional person!! I will soon post a detail note about the issue I faced and resolved with the help of Laerte. Here is his solution to my earlier puzzle in his own words. Read the original puzzle here and Laerte’s solution from here. Hi Pinal, I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be: 1 – All indexes in all tables and all databases Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused 2 – All Indexes in all tables and specific database Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused 3 – All Indexes in specific table and database Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused and to output to txt.. pipe Out-File Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:\IndexesSize.txt If you have one txt with all your servers, can be for all of them also. Lets say you have all your servers in servers.txt: something like NameServer1 NameServer2 NameServer3 NameServer4 We could Use : foreach ($Server in Get-content c:\temp\servers.txt) { Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused } :) After fixing my issue with Powershell, I ran Laerte‘s second suggestion – “All Indexes in all tables and specific database” and found the following accurate output. Click to Enlarge Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Powershell

  Read the article

< Previous Page | 3 4 5 6 7 8 9 10 11 12 13 14  | Next Page >