Search Results

Search found 617 results on 25 pages for 'postaday'.

Page 4/25 | < Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• SQLAuthority News – 2 New Books – FREE Books and Book Signing at #SQLPASS 2012

  - by pinaldave

  As an author the most interesting task is to participate in Book Signing Events. If you are at SQLPASS – we are going to have a lot of book signing events. Here is the good news! MY NEW BOOKS ARE OUT! SQL 2012 Functions Limited Edition Click to Expand This book is a very special edition book. Our current plans is to run this book for the limited edition. You can avail this book from Amazon and it will soon come to India. Join following book signing events where you will get this book for free. Wednesday, November 7, 2012 7pm-8pm - Embarcadero Booth Book Signing (FREE BOOK) Thursday, November 8, 2012 12pm-1pm - Embarcadero Booth Book Signing (FREE BOOK) SQL Queries 2012 Joes 2 Pros Volume1 Click to Expand This is my first book this year which will be available in bookstores. Last Year I published 3 books and this year this is my first book. This book is available on Amazon over here and it will come to India very soon. Join following book signing events where you will get this book for free. Wednesday, November 7, 2012 12pm-1pm – Book Signing at Exhibit Hall Joes Pros booth#117 (FREE BOOK) If you are attending SQLPASS you may get this book’s Autographed Special Edition for FREE if you attend following book signing events. Rest all the time – I will be at Exhibition Hall Joes 2 Pros Booth #117. Stop by for the goodies! Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL PASS, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, SQLAuthority News, SQLServer, T SQL, Technology

  Read the article

• SQLAuthority News – 2 Security Updates for SQL Server 2000 SP 4 Users

  - by pinaldave

  If you are using SQL Server 2000 still today my very first recommendation to you is to upgrade to SQL Server 2012. SQL Server 2000 is now 12 years old product and since then many new enhancements as well features which are relevant to current growth and progress in Informational Industry. Now is the time to catch up with the latest trends. Here is one more point for you to notice if this helps you consider to upgrade to the latest version. One can’t upgrade directly from SQL Server 2000 to SQL Server 2012. You need to first upgrade to either SQL Server 2005/2008/R2 and then further plan to upgrade to SQL Server 2012. There is no direct upgrade path for SQL Server 2000 to SQL Server 2012. I strongly believe this is the time to upgrade to the latest version. Well, also there is a rule that to let something continue if it is not broken and working fine. If you are following that rule and still using SQL Server 2000 I strongly suggest that you upgrade your SQL Server 2000 SP4 and update it with latest Security updates. Here are two important SQL Server Security Updates. Security Update for SQL Server 2000 Service Pack 4 (KB983811) Security Update for SQL Server 2000 Service Pack 4 (KB983812) As we are talking about SQL Server 2000 let me ask you a quick question – how many of you are still using SQL Server 2000 or earlier version in a production system on at least one server? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Download, SQL Query, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQLAuthority News – TechED India 2012 – Bangalore – March 21-23, 2012

  - by pinaldave

  TechEd is one event which every developers and IT professionals are looking forward to attend. It is opportunity of life time and no matter how many time one gets chance to engage with it, it is never enough. I still remember every single moment of every TechEd I have attended so far. This year TechEd India 2012 will be held in Bangalore between March 21 and 23. There will be three 3 days of lots of learning and fun. If you are data professional, you are going to find yourself very very fortunate as every single day we will have data track for various audience. Day 1 will be for developer, Day 2 will be for Architect and Day 3 will be for Database Administrators. Every day we will have plenty of learning from industries leading experts. How many of you know that the first TechEd was held in 1993 in Orlando, FL? Well, there are many similar interesting information is available on Wiki page for TechEd. I will be presenting on my favorite subject of performance tuning. Just like every other time this time the session will be unique and different. I will bring something lesser known but very important aspect of the performance tuning to the light. Besides SQL Server we will be covering lots of other technologies such as Windows 8, Windows Phone, Windows Azure, Visual Studio, System Center, Security, Private Cloud etc. The biggest attraction of the TechEd is Keynote and Demo Extravaganza. One can not miss either of them when present at TechEd India. If you are attending TechEd India – I am looking forward to meet you in person. It is always pleasant to meet community face to face and I promise to remember your name. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology

  Read the article

• SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file

  - by pinaldave

  Earlier, I wrote a quite note on SQL SERVER – Detect Virtual Log Files (VLF) in LDF. Because of this I got responses suggesting too many VLFs are bad for log file. This prompts to a simple question: “How many is ‘too many’ VLFs?” I suggest that you go and read an article written by Kimberly over here. I am sure that you are going to have a clear understanding of what a good number for your VLFs is from that article. If you have lots of VLFs, you can reduce them right away using the following method: (I am just attempting to write a working script over here.) USE AdventureWorks GO BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak' GO -- Get Logical file name of the log file sp_helpfile GO DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY) GO ALTER DATABASE AdventureWorks MODIFY FILE (NAME = AdventureWorks_Log,SIZE = 1GB) GO DBCC LOGINFO GO Again, here I have assumed that your initial log size is 1 GB, but in reality you should select the number based on your own ideal size of the log file. If your log file grows to 10 GB every day, you may want to put the value as 10 GB. For accuracy, read what Kimberly’s original article says over here. 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 Contest – Win 10 Amazon Gift Cards worth (USD 200) and 10 NuoDB T-Shirts

  - by Pinal Dave

  This month, we have yet not run any contest so we will be running a very interesting contest with the help of good guys at NuoDB. NuoDB has just released version 2.0 and You can download NuoDB from here. NuoDB’s NewSQL distributed database is designed to be a single database that works across multiple servers, which can scale easily, and scale on demand. That’s one system that gives high connectivity but no latency, complexity or maintenance issues. MySQL works in some circumstances, but a period of growth isn’t one of them. So as a company moves forward, the MySQL database can’t keep pace. Data storage and data replication errors creep in. Soon the diaspora of the offices becomes a problem. Your telephone system isn’t just distributed, it is literally all over the place. You can read my detailed article about how Why VoIP Service Providers Should Think About NuoDB’s Geo Distribution. Here is the contest: Contest Part 1: NuoDB R2.0 delivered a long list of improvements and new features. List three of the major features of NuoDB 2.0. Here is the hint1, hint2, hint3. Contest Part 2: Download NuoDB using this link. Once you download NuoDB, leave a comment over here with the name of the platform and installer size. (For example Windows Platform Size abc.dd MB) Here is the what you can win! Giveaways 10 Amazon Gift Card (Each of USD 20 – total USD 200) 10 Amazingly looking NuoDB T-Shirts (For the first 10 downloads) Rules Participate before Oct 28, 2013. All the valid answers will be published after Oct 28, 2013 and winners will receive an email on Nov 1st, 2013. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: NuoDB

  Read the article

• SQL SERVER – Find First Non-Numeric Character from String

  - by pinaldave

  It is fun when you have to deal with simple problems and there are no out of the box solution. I am sure there are many cases when we needed the first non-numeric character from the string but there is no function available to identify that right away. Here is the quick script I wrote down using PATINDEX. The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character. -- How to find first non numberic character USE tempdb GO CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100)) GO INSERT INTO MyTable (ID, Col1) SELECT 1, '1one' UNION ALL SELECT 2, '11eleven' UNION ALL SELECT 3, '2two' UNION ALL SELECT 4, '22twentytwo' UNION ALL SELECT 5, '111oneeleven' GO -- Use of PATINDEX SELECT PATINDEX('%[^0-9]%',Col1) 'Position of NonNumeric Character', SUBSTRING(Col1,PATINDEX('%[^0-9]%',Col1),1) 'NonNumeric Character', Col1 'Original Character' FROM MyTable GO DROP TABLE MyTable GO Here is the resultset: Where do I use in the real world – well there are lots of examples. In one of the future blog posts I will cover that as well. Meanwhile, do you have any better way to achieve the same. Do share it here. I will write a follow up blog post with due credit to you. Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL String, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode

  - by Pinal Dave

  Here is the question which I received the other day on SQLAuthority Facebook page. Social media is a wonderful thing and I love the active conversation between blog readers and myself – actually I think social media adds lots of human factor to any conversation. Here is the question - “I am using sqlcmd in SSMS – I am not sure how to declare variable and pass it, for example I have a database and it has table, how can I make the table variable dynamic and pass different value everytime?” Fantastic question, and here is its very simple answer. First of all, enable sqlcmd mode in SQL Server Management Studio as described in following image. Now in query editor type following SQL. :SETVAR DatabaseName “AdventureWorks2012″ :SETVAR SchemaName “Person” :SETVAR TableName “EmailAddress“ USE $(DatabaseName); SELECT * FROM $(SchemaName).$(TableName); Note that I have set the value of the database, schema and table as a sqlcmd variable and I am executing the query using the same parameters. Well, that was it, sqlcmd is a very simple language to master and it also aids in doing various tasks easily. If you have any other sqlcmd tips, please leave a comment and I will publish it with due credit. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology Tagged: sqlcmd

  Read the article

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

  - by pinaldave

  SQL Server 2012 introduces new analytical function PERCENTILE_CONT(). The book online gives following definition of this function: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset in Microsoft SQL Server 2012 Release Candidate 0 (RC 0). For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P. If you are clear with understanding of the function – no need to read further. If you got lost here is the same in simple words – it is lot like finding median with percentile value. Now let’s have fun following query: USE AdventureWorks GO SELECT SalesOrderID, OrderQty, ProductID, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ProductID) OVER (PARTITION BY SalesOrderID) AS MedianCont FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY SalesOrderID DESC GO The above query will give us the following result: You can see that I have used PERCENTILE_COUNT(0.5) in query, which is similar to finding median. Let me explain above diagram with little more explanation. The defination of median is as following: In case of Even Number of elements = In ordered list add the two digits from the middle and devide by 2 In case of Odd Numbers of elements = In ordered list select the digits from the middle I hope this example gives clear idea how PERCENTILE_CONT() works. 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 – Attach or Detach Database – SQL in Sixty Seconds #068

  - by Pinal Dave

  When we have to move a database from one server to another server or when we have to move a database from one file to another file, we commonly use Database Attach or Detach process. I have been doing this for quite a while as well. Recently, when I was visiting an organization I found that in this organization lots of developers are still using an older version of the code to attach the database. I quickly pointed that out to them the new method to attach the database, however it was really interesting to find out that they really did not know that sp_attach_db is now a deprecated method to attach the database. This really made me to do today’s SQL in Sixty Seconds. I demonstrate in this SQL in Sixty Seconds how to attach or detach the database using a new method of attaching database. The code which I have used in this code is over here: -- Detach Database USE [master] GO EXEC MASTER.dbo.sp_detach_db @dbname = N'AdventureWorks2014_new' GO -- Deprecated Way to Attach Database USE [master] GO EXEC MASTER.dbo.sp_attach_db 'AdventureWorks2014_new', 'E:\AdventureWorks2012_Data_new.mdf', 'E:\AdventureWorks2012_log_new.ldf' GO -- Correct Way to Attach Database USE [master] GO CREATE DATABASE [AdventureWorks2014_new] ON ( FILENAME = 'E:\AdventureWorks2012_Data_new.mdf'), ( FILENAME = 'E:\AdventureWorks2012_log_new.ldf') FOR ATTACH GO Here is the question back to you – Do you still use old methods to attach database? If yes, I suggest that you start using the new method onwards. SQL in Sixty Seconds Video I have attempted to explain the same subject in simple words over in following video. Action Item Here are the blog posts I have previously written on the subject of SA password. You can read it over here: SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database SQL SERVER – Move Database Files MDF and LDF to Another Location SQL SERVER – 2005 Take Off Line or Detach Database SQL SERVER – Attach mdf file without ldf file in Database SQL SERVER – Copy Database from Instance to Another Instance – Copy Paste in SQL Server You can subscribe to my YouTube Channel for frequent updates. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Book Review, SQLAuthority News, T SQL, Video

  Read the article

• SQL SERVER – Take the Quiz for a chance to win a Quadcopter Drone – Brain Teasers

  - by Pinal Dave

  It has been a long time since we ran quiz. So let us get ready for a quiz. The quiz has two parts. You have to get both the parts correct to win Quadcopter with Camera (we will call it drone). We will be giving away a total of 2 Quadcopters. The quiz is extremely easy and I will ship the Drone anywhere in the world where Amazon will ship it. Let us jump directly to the quiz. Please complete all the three questions of the contest.  Contest Part 1: Brain Teasers There are two questions for you in this part of the contest. Question: There are two 7s. How will you write select statement with a single operator that returns single 7? Hint: SELECT 7(Answer)7 Question: Write down the shortest code that produces 1 without using any numbers in the select statement? Hint: SELECT (Answer) Contest Part 2: Download and Activate Rapid SQL Question: Download and Activate Rapid SQL. Hint: You have to download and activate Rapid SQL. If you do not activate Rapid SQL, you will be disqualified for the contest. Why take risk, let us start! That’s it! Just answer above questions in the following comments area, in following format. Remember: Use comments area right below the blog to take participation in the contest Answer before June 5, 2014 midnight GMT. The winner will be announced on June 8. The winner will be selected randomly from all the valid answers. All the valid answers will be kept hidden till June 5, 2014. There will be a total of two winners. The contest is open for any country of the world where Amazon ships products. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

  Read the article

• SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video

  - by pinaldave

  Collation is a very important concept but often ignored. I have often seen developers either not understanding this or ignored it – this is plain wrong. In simple word we can say Collation is the language or interpreting done by SQL Server. Well, in today’s SQL in Sixty Seconds we are going to observe how collation affects the resultset. Today’s blog post is inspired from my earlier blog post SQL SERVER – Effect of Case Sensitive Collation on Resultset. I strongly encourage you to read this earlier blog post for sample code as well additional explanation related to the concept shared in today’s SQL in Sixty Seconds. Here is the code used in the video. USE TempDB GO -- Sample Data Building CREATE TABLE ColTable (Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS, Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ; INSERT ColTable(Col1, Col2) VALUES ('Apple','Apple'), ('apple','apple'), ('pineapple','pineapple'), ('Pineapple','Pineapple'); GO -- Retrieve Data SELECT * FROM ColTable GO -- Retrieve Data SELECT * FROM ColTable ORDER BY Col1 GO -- Retrieve Data SELECT * FROM ColTable ORDER BY Col2 GO -- Clean up DROP TABLE ColTable GO Related Tips in SQL in Sixty Seconds: SQL SERVER – Effect of Case Sensitive Collation on Resultset Example of Width Sensitive and Width Insensitive Collation Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 Change Collation of Database Column – T-SQL Script Find Collation of Database and Table Column Using T-SQL Default Collation of SQL Server 2008 Cannot resolve collation conflict for equal to operation 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 – Function: Is Function – SQL in Sixty Seconds #004 – Video

  - by pinaldave

  Today is February 29th. An unique date which we only get to observe once every four year. Year 2012 is leap year and SQL Server 2012 is also releasing this year. Yesterday I wrote an article where we have seen observed how using four different function we can create another function which can accurately validate if any year is leap year or not. We will use three functions newly introduced in SQL Server 2012 and demonstrate how we can find if any year is leap year or not. This function uses three of the SQL Server 2012 functions - IIF, EOMONTH and CONCAT. When I wrote this function, this is the sortest function I ever wrote to find out leap year. Please watch the video and let me know if any shorter function can be written to find leap year. More on Leap Yer: Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function Date and Time Functions – EOMONTH() – A Quick Introduction Script/Function to Find Last Day of Month  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 – Question to You – When to use Function and When to use Stored Procedure

  - by pinaldave

  This week has been very interesting week. I have asked few questions to users and have received remarkable participation on the subject. Q1) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*) Q2) SQL SERVER – Puzzle – Statistics are not Updated but are Created Once Keeping the same spirit up, I am asking the third question over here. Q3) When to use User Defined Function and when to use Stored Procedure in your development? Personally, I believe that they are both different things - they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question. When do you use Function and when do you use Stored Procedure? What are Pros and Cons of each of them when used instead of each other? If you are going to answer that ‘To avoid repeating code, you use Function’ - please think harder! Stored procedure can do the same. In SQL Server Denali, even the stored procedure can return the result just like Function in SELECT statement; so if you are going to answer with ‘Function can be used in SELECT, whereas Stored Procedure cannot be used’ - again think harder! (link). Now, what do you say? I will post the answers of all the three questions with due credit next week. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, Readers Question, SQL, SQL Authority, SQL Function, SQL Puzzle, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLServer, T SQL, Technology

  Read the article

• SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor

  - by pinaldave

  Working in performance tuning area, one has to know about Index and Index Maintenance. For any Index the most important property is Fill Factor. Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100. I have written following two article about Fill Factor. What is Fill factor? – Index, Fill Factor and Performance – Part 1 What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2 I strongly encourage read them and provide your feedback. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Index, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Where Can YOU Get My Books – SQL Server Interview Question and Answers

  - by pinaldave

  Earlier month I released by third book SQL Server Interview Question and Answers. The focus of this book is ‘master the basics’. If you rate yourself 10 out of 10 in SQL Server – this book is not for you but if you want to learn fundamentals or want to refresh your fundamentals this book is for YOU. Earlier I was overwhelmed by love you all have shown to this book on release date leading our three digit inventory to run out of stock. Read detail blog post about the subject over here A Real Story of Book Getting ‘Out of Stock’ to A 25% Discount Story Available. Well, we learn the lesson from the experience and have made sure that the inventory does not run out any more. Since then we are now available on multiple outlets. Pretty much anywhere in USA and India the book is available. Additionally, where ever Amazon ships internationally. I have created dedicated page where I have listed where one can avail this book from Details of SQL Server Interview Question and Answers. Even though I keep on getting common question like – where one can get this book. You can get this book from: USA: Amazon India: Flipkart | IndiaPlaza | Crossword In India now you can walk into any crossword store and ask this book, if they do not have it, you can ask them get one for you. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Best Practices, Pinal Dave, PostADay, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority Book Review, SQLAuthority News, T SQL, Technology

  Read the article

• SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

  - by Pinal Dave

  A very common question I often receive is how to detect if the disk space is running low on SQL Server. There are two different ways to do the same. I personally prefer method 2 as that is very easy to use and I can use it creatively along with database name. Method 1: EXEC MASTER..xp_fixeddrives GO Above query will return us two columns, drive name and MB free. If we want to use this data in our query, we will have to create a temporary table and insert the data from this stored procedure into the temporary table and use it. Method 2: SELECT DISTINCT dovs.logical_volume_name AS LogicalName, dovs.volume_mount_point AS Drive, CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs ORDER BY FreeSpaceInMB ASC GO The above query will give us three columns: drive logical name, drive letter and free space in MB. We can further modify above query to also include database name in the query as well. SELECT DISTINCT DB_NAME(dovs.database_id) DBName, dovs.logical_volume_name AS LogicalName, dovs.volume_mount_point AS Drive, CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs ORDER BY FreeSpaceInMB ASC GO This will give us additional data about which database is placed on which drive. If you see a database name multiple times, it is because your database has multiple files and they are on different drives. You can modify above query one more time to even include the details of actual file location. SELECT DISTINCT DB_NAME(dovs.database_id) DBName, mf.physical_name PhysicalFileLocation, dovs.logical_volume_name AS LogicalName, dovs.volume_mount_point AS Drive, CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs ORDER BY FreeSpaceInMB ASC GO The above query will now additionally include the physical file location as well. As I mentioned earlier, I prefer method 2 as I can creatively use it as per the business need. Let me know which method are you using in your production server. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

  Read the article

• SQL SERVER – Copy Statistics from One Server to Another Server

  - by pinaldave

  I was recently working on a performance tuning project in Dubai (yeah I was able to see the tallest tower from the window of my work place). I had a very interesting learning experience there. There was a situation where we wanted to receive the schema of original database from a certain client. However, the client was not able to provide us any data due to privacy issues. The schema was very important because without having an access to underlying data, it was a bit difficult to judge the queries etc. For example, without any primary data, all the queries are running in 0 (zero) milliseconds and all were using nested loop as there were no data to be returned. Even though we had CPU offending queries, they were not doing anything without the data in the tables. This was really a challenge as I did not have access to production server data and I could not recreate the scenarios as production without data. Well, I was confused but Ruben from Solid Quality Mentors, Spain taught me new tricks. He suggested that when table schema is generated, we can create the statistics consequently. Here is how we had done that: Once statistics is created along with the schema, without data in the table, all the queries will work as how they will work on production server. This way, without access to the data, we were able to recreate the same scenario as production server on development server. When observed at the script, you will find that the statistics were also generated along with the query. You will find statistics included in WITH STATS_STREAM clause. What a very simple and effective script. 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, SQLServer, T SQL, Technology Tagged: SQL Statistics, Statistics

  Read the article

• SQL SERVER- Differences Between Left Join and Left Outer Join

  - by pinaldave

  There are a few questions that I had decided not to discuss on this blog because I think they are very simple and many of us know it. Many times, I even receive not-so positive notes from several readers when I am writing something simple. However, assuming that we know all and beginners should know everything is not the right attitude. Since day 1, I have been keeping a small journal regarding questions that I receive in this blog. There are around 200+ questions I receive every day through emails, comments and occasional phone calls. Yesterday, I received a comment with the following question: What are the differences between Left Join and Left Outer Join? Click here to read original comment. This question has triggered the threshold of receiving the same question repeatedly. Here is the answer: There is absolutely no difference between LEFT JOIN and LEFT OUTER JOIN. The same is true for RIGHT JOIN and RIGHT OUTER JOIN. When you use LEFT JOIN keyword in SQL Server, it means LEFT OUTER JOIN only. I have already written in-depth visual diagram discussing the JOINs. I encourage all of you to read the article for further understanding of the JOINs: Read Introduction to JOINs – Basic of JOINs Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – New SQL Server 2012 Functions – Webinar by Rick Morelan

  - by Pinal Dave

  My friend Rick Morelan is a wonderful speaker and listening to him is very delightful. Rick is one of the speakers who can articulate a very complex subject in very simple words. Rick has attained over 30 Microsoft certifications in applications, networking, databases and .NET development, including MCDBA, MCTS, MCITP, MCAD, MOE, MCSE and MCSE+. Here is the chance for every one who has not listened Rick Morelan before as he is presenting an online webinar on New SQL Server 2012 Functions. Whether or not you’re a database developer or administrator, you love the power of SQL functions. The functions in SQL Server give you the power to accelerate your applications and database performance. Each version of SQL Server adds new functionality, so come and see Rick Morelan explain what’s new in SQL Server 2012! This webinar will focus on the new string, time and logical functions added to SQL Server 2012. Register for the webinar now to learn: SQL Server 2012 function basics String, time and logical function details Tools to accelerate the SQL coding process Tuesday June 11, 2013  7:00 AM PDT / 10:00 AM EDT 11:00 AM PDT / 2:00 PM EDT Secret Hint: Here is something I would like to tell everyone that there is a quiz coming up on SQLAuthority.com and those who will attend the webinar will find it very easy to resolve it. Register for webinar Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Download Free eBook – Introducing Microsoft SQL Server 2012

  - by pinaldave

  Database Administration and Business Intelligence is indeed very key area of the SQL Server. My very good friend Ross Mistry and Stacia Misner has recently wrote book which is for SQL Server 2012. The best part of the book is it is totally FREE! Well, this book assumes that you have certain level of SQL Server Administration as well Business Intelligence understanding. So if you are absolutely beginner I suggest you read other books of Ross as well attend Pluralsight course of Stacia Misner. Personally I read this book in last 10 days and I find it very easy to read and very comprehensive as well. Part I Database Administration (by Ross Mistry) 1. SQL Server 2012 Editions and Engine Enhancements 2. High-Availability and Disaster-Recovery Enhancements 3. Performance and Scalability 4. Security Enhancements 5. Programmability and Beyond-Relational Enhancements Part II Business Intelligence Development (by Stacia Misner) 6. Integration Services 7. Data Quality Services 8. Master Data Services 9. Analysis Services and PowerPivot 10. Reporting Services Here are various versions of the eBook. PDF ePub Mobi Amazon Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Database, PostADay, SQL, SQL Authority, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – 2012 RC0 Various Resources and Downloads

  - by pinaldave

  Microsoft SQL Server 2012 Release Candidate 0 (RC0) Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization. Microsoft SQL Server 2012 Express RC Microsoft SQL Server 2012 Express RC0 is a powerful and reliable free data management system that delivers a rich set of features, data protection, and performance for embedded applications, lightweight Web Sites, applications, and local data stores. Microsoft SQL Server 2012 Semantic Language Statistics RC0 The Semantic Language Statistics Database is a required component for the Statistical Semantic Search feature in Microsoft SQL Server 2012 Semantic Language Statistics RC0. Microsoft SQL Server 2012 Release Candidate 0 (RC0) Manageability Tool Kit The Microsoft SQL Server 2012 Release Candidate 0 (RC0) Manageability Tool Kit is a collection of stand-alone packages which provide additional value for Microsoft SQL Server 2012 Release Candidate 0 (RC0). Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010 Release Candidate 0 (RC0) Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology; fast manipulation of large data sets, streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Database, PostADay, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• MySQL – Introduction to User Defined Variables

  - by Pinal Dave

  MySQL supports user defined variables to have some data that can be used later part of your query. You can save a value to a variable using a SELECT statement and later you can access its value. Unlike other RDBMSs, you do not need to declare the data type for a variable. The data type is automatically assumed when you assign a value. A value can be assigned to a variable using a SET command as shown below SET @server_type:='MySQL'; When you above command is executed, the value, MySQL is assigned to the variable called @server_type. Now you can use this variable in the later part of the code. Suppose if you want to display the value, you can use SELECT statement. SELECT @server_type; The result is MySQL. Once the value is assigned it remains for the entire session until changed by the later statements. So unlike SQL Server, you do not need to have this as part the execution code every time. (Because in SQL Server, the variables are execution scoped and dropped after the execution). You can give column name as below SELECT @server_type AS server_type; You can also SELECT statement to DECLARE and SELECT the values for a variable. SELECT @message:='Welcome to MySQL' AS MESSAGE; The result is Message -------- Welcome to MySQL You can make use of variables to effectively apply many logics. One of the useful method is to generate the row number as shown in this post MySQL – Generating Row Number for Each Row using Variable. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: MySQL, PostADay, SQL, SQL Authority, SQL Query, SQL Tips and Tricks, T SQL

  Read the article

• SQL SERVER – Demo Script – Keeping CPU Busy

  - by pinaldave

  Recently face very interesting situation, during presentations at event, I was asked very famous questions: “My CPU is very high all the time, how can I reduce it?” This is very interesting question and there are many answers and a single blog post is not good enough to justify this subject. I presented few situation to the person who asked the question. The member of the audience who asked question came to me afterwords and asked me few detailed questions. To answer him, I quickly wrote query which simulate high CPU. Here is the script which I wrote which increased CPU from 10% to 80%. I was wondering if there is any similar script which can simulate high CPU usage. If you have share with me and I will publish with due credit. Here is my script for the same: USE AdventureWorks GO DECLARE @Flag INT SET @Flag = 1 WHILE(@Flag < 1000) BEGIN ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] REBUILD SET @Flag = @Flag + 1 END GO   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 – 2008 – Unused Index Script – Download

  - by pinaldave

  Download Missing Index Script with Unused Index Script Performance Tuning is quite interesting and Index plays a vital role in it. A proper index can improve the performance and a bad index can hamper the performance. Here is the script from my script bank which I use to identify unused indexes on any database. Please note, if you should not drop all the unused indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense. Any way, the scripts is good starting point. You should pay attention to User Scan, User Lookup and User Update when you are going to drop index. The generic understanding is if this values are all high and User Seek is low, the index needs tuning. The index drop script is also provided in the last column. Download Missing Index Script with Unused Index Script -- Unused Index Script -- Original Author: Pinal Dave (C) 2011 SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC GO Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Download, SQL Index, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority

  - by pinaldave

  SQL Server 2012 (RC0 Available here) has introduced new analytic functions. These functions were long awaited and I am glad that they are here. Previously when any of this function was needed people use to write long T-SQL code to simulate that and now no need of the same. Having available native function also helps performance as well readability. In last few days I have written many articles on this subject on my blog. The goal was make these complex analytic functions easy to understand and make it widely accepted. As this new functions are available and as awareness spreads we should start using the new functions. Here is the quick list of the new function and relevant MSDN site. Function SQLAuthority MSDN CUME_DIST CUME_DIST CUME_DIST FIRST_VALUE FIRST_VALUE FIRST_VALUE LAST_VALUE LAST_VALUE LAST_VALUE LEAD LEAD LEAD LAG LAG LAG PERCENTILE_CONT PERCENTILE_CONT PERCENTILE_CONT PERCENTILE_DISC PERCENTILE_DISC PERCENTILE_DISC PERCENT_RANK PERCENT_RANK PERCENT_RANK I also enjoyed three different puzzles during the course of this series which gave clear idea to the SQL Server 2012 analytic functions. SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG SQL SERVER – Puzzle to Win Print Book – Explain Value of PERCENTILE_CONT() Using Simple Example This series will be always my dear series as during this series I had went through very unique experience of my book going out of stock and becoming available after 48 hours. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

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