These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Jonathan RozenblitDeveloper Evangelist
Susan IbachDeveloper Evangelist
I recently saw a post on Stack Overflow asking how do you copy specific table data from one database to another in SQL Server. It struck me I should share the solution to this with others because it is such a handy trick. Often I set up test data and want to quickly copy it to another table, or a co-worker wants a copy of my data, or I want to copy some data from production to a local database.
If all you want to do is copy data from one table to another in the same database, just use the INSERT clause on the SELECT statement.
INSERT INTO PlayerBackups
SELECT * FROM NhlPlayer
If you do not have a second table and you want to make a quick and dirty backup of some test data, you can create a table based on the data you choose in your select statement.
SELECT * INTO PlayerBackups
If you want to move data between tables across databases, you will have to use a fully qualified name
INSERT INTO YourDatabase.YourSchema.PlayerBackups
SELECT * FROM MyDatabase.MySchema.NhlPlayer
If you want to move data across servers, you will need to set up a linked server, this will require working with the DBA because there are authentication issues around linked servers (how will your account log in to the other server? what permissions will you have on the other server?) Once you have a linked server set up, then you can just use the fully qualified name to specify the server name.
INSERT INTO YourServer.YourDatabase.YourSchema.PlayerBackups
SELECT * FROM MyServer.MyDatabase.MySchema.NhlPlayer
I am always forgetting the syntax for these commands, so thought I would share them. Don’t forget, if you know SQL, you know SQL Azure! Try it out now
Although not a new feature, Indexed views can still be a useful tool for increasing query performance. Of course you have to be careful of the trade-offs. Just like indexing a table, indexing a view may speed up a query, but will increase your storage requirements and slow down insert and update operations. So make sure you benchmark performance before and after you add an index to a view.
With that caveat in mind, I’d like to do a review of Indexed Views because of a post I saw from a SQL User having trouble creating a Fulltext index on an indexed view.
When we create a view we simply specify the select statement that will return the data we want displayed in the view.
CREATE VIEW OrderInfo
AS SELECT od.SalesOrderId, od.productid, od.unitprice, od.orderqty, p.name
FROM Sales.SalesOrderDetail od
ON od.productid =p.productid
If you want to add indexes to a view you must make the view schema bound by adding the WITH SCHEMABINDING clause to the CREATE VIEW statement and you must specify the schema for each table specified in the select statement for the view.
CREATE VIEW OrderInfo WITH SCHEMABINDING
AS SELECT od.SalesOrderId, od.productid, od.unitprice, od.orderqty, p.name
FROM Sales.SalesOrderDetail od
ON od.productid =p.productid
The first index you create on a view must be a unique clustered index. So in this example I create a unique clustered index on the combination of SalesOrderId and ProductId
CREATE UNIQUE CLUSTERED INDEX idx_orderinfo_salesOrderid
I can now add additional nonclustered indexes as desired to the view
CREATE NONCLUSTERED INDEX idx_unitprice
Now coming back to the question posted on reddit, can you create a fulltext index on the view? The steps for full text indexes changed quite a bit for SQL Server 2008 from SQL Server 2005. The steps listed below are for SQL Server 2008 and higher where full text indexing no longer requires a separate service and is enabled automatically.
In order to create a full text index you first need a full text catalog, unless you have already created one for other fulltext indexes in your database.
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT
Next I try to create a fulltext index on the product name column, you must specify the name of the column you wish to index and the name of the unique clustered index for the view.
CREATE FULLTEXT INDEX ON dbo.orderinfo(name)
KEY INDEX idx_orderinfo_salesorderid
At this point I receive an error message, because there are restrictions on the key indexes used for creating full text indexes. The key index must be:
My key index is based on two columns, so I am unable to create a full text index for this view. So can you create a full text index on a view? It depends. If my view above had a key index that met the requirements listed above then yes! If my key index does not meet the requirements I may need to redesign my index or my view so that I can create a key index that meets the requirements.
So we finish with everyone’s favourite answer. It depends. Don’t forget if you know SQL you know SQL Azure, read about the differences between on premise SQL Server and SQL Azure database development and you will find it’s easier than you think.
The most common question I get when talking about certification is what should I study? Well, before you crack open a book, take the time to make sure you are taking the right exam, and figure out what you don’t know so your study time will be spent as effectively as possible. If you want get certified, there is a simple plan you can follow:
In the last blog, “How to Prepare for a Certification Exam” I talked about how to figure out what you don’t know. That blog explained how to determine what topics are on the exam, and how to prioritize the topics to study. Now it’s time to look at Step Three: Filling in the Gaps.
You’ve completed step two “Figure out what you don’t know”, so you should now have a list of the topics you need to study. But where will you find suitable study materials? There are many, many resources out there to help you prepare for exams. Let’s review a few key resources.
Every exam guide has a section called Preparation Materials that lists all the MsPress books, e-learning, and courses you can access that include content covered in the exam. Now it’s important to note that just because a course or book is listed here that taking that course or reading that book guarantees you will pass the exam. You know what topics you need to study. These are suggested resources. You need to go look at the course outlines to see if they cover the areas you need to study, you need to see the table of contents for that book to see if it has sections on the areas YOU need to study. The courses and books are a great overall review, but unless you take a boot camp course which is designed to help you prepare for a specific exam, you should assume the course only covers some of the exam content. So you still need to find your gaps and fill those gaps. Some exams, like this SharePoint 2010 exam, also have Exam Coaching Sessions with tips on that specific exam you can watch.
The more popular exams have books called Self-Paced Training Kits that are specifically designed to help you prepare for an exam. They include exercises and practice tests to help you study and check your knowledge at the same time. If you are going to invest in a book to help you prepare, and there is a training kit for your exam, these are often a worthwhile investment. If you already have deep knowledge of the product and just need to learn a few specific topics, the training kit is probably more than you need.
Practice Exams are also a great study tool. Both MeasureUp and SelfTest give you the option of completing practice test in a learning or study mode. These are a great study tool because after you answer each question you can check your answer and then read an explanation of why each answer was wrong or right. For the maximum value from a practice test, I recommend customizing your test. Both Self Test and MeasureUp give you the option of specifying which topics you want on your test. If there is a specific content area you know you need to study, customize your test to ask you all the questions on that topic.
The Microsoft Learning Training Catalog is another great place to search for exam preparation materials. You can search this catalog to get a list of learning materials related to your technology. You can even create a My Learning account to help you remember which resources you want to review and help you keep track of which white papers or articles you have read. You can search by technology or by exam number, and you can customize the search to change what types of resources are returned in the search results.
One of the resources you may see displayed in your training catalog search results is a Learning Plan. Many exams have Learning Plans associated with them. Learning plans are designed to identify resources to help you achieve a goal like passing an exam, or learning a new version of a tool. Learning plans are an often overlooked resource, and can point you to articles or whitepapers that you might otherwise have missed. In order to follow the links on a learning plan, sign in with a Windows Live ID and save the training plan to My Learning, then you can go to the articles and keep track of which content you have already read.
Reading is great, but actually trying out a feature is always the best way to learn if you have time. Many product teams provide virtual machines you can download, and TechDays Canada will be hosting a number of Hands On Labs on developer and infrastructure tools throughout the year you can complete online.
Today’s My 5 covers a situation that occasionally comes up with new exams:
When a certification exam is first released, sometimes you go to the Preparation Materials tab and there are no books, no courses, and no practice tests. So now what? Well first of all you have earned my respect because you are probably going to pass this exam before most of your peers. But what can you study?
Next blog we will wrap up our certification discussion and talk about what to expect on the day of the exam and a few tips to increase your chances of walking out with a passing score.
For many people taking an exam can be really stressful. In this blog post I’d like to see if I can take some of the fear away from taking that exam. This is the final post in my series on How to get certified. Before you take the exam you should complete the first 3 steps. Let’s be clear, I said before you TAKE the exam complete the first 3 steps. I recommend scheduling the exam as soon as you have chosen your certification goal (step 1). By scheduling your exam you commit yourself to a date and suddenly you have a target, instead of taking the exam 'someday’ you now know exactly when you are taking the exam. Just setting a deadline makes you more likely to meet your certification goal. We have four steps to complete to earn a certification:
We’ve talked about how to prepare in the blog posts describing the first three steps, now it’s time to focus on the exam itself.
You schedule your exam at a Prometric testing center. They have locations across the country. When you visit their website to schedule an exam you will be provided with a list of testing center locations to choose from. After you select a location you will see a calendar which displays the available exam times at that testing center. The website also gives you the option of phoning if you need help scheduling your exam. You will also find answers on the website to frequently asked questions such as how much does the exam cost? and What is the policy for rescheduling the exam. Keep an eye on the Microsoft Learning site for promotions that might give you a discount or a free second try of your exam if you don’t pass the first time.
What’s that? Did I say “If you don’t pass!” Yup, I think that is everyone’s greatest fear, failing the exam. Okay now think about this for a second, if, worst comes to worst, and you don’t pass. You are out the cost of the exam. You will not be the first or the last person to fail an exam. Take a deep breath, shrug your shoulders, look at your exam score sheet to check where you scored well, and where you need to study. You just completed Step 2: Figure out what you don’t know, now re-execute Step 3: fill in the gaps with some more studying, and try again.
But let’s do everything we can to help you pass the first time! You’ve scheduled your exam, you’ve researched what is on the exam, you’ve studied the topic areas to fill in the gaps of your knowledge, now it is time to walk through that door and take the exam itself.
I recommend giving yourself extra time to get to the testing center. Plan on arriving 30 minutes early, that way if you do get lost or stuck in traffic you will still make it on time. If you arrive early, all the better, you can fill out the paperwork, and sit down for a few minutes reviewing a few notes before you start. Don’t forget to bring government issued Photo ID and the exam confirmation information which has your Prometric ID, Exam number, and start time.
At your appointed start time you will be asked to hand over your cell phone, and any bags such as a purse or backpack you have with you. You will be given either a pad of paper and a pen, or a plasticized piece of paper and a whiteboard marker and eraser that you can use to take notes for yourself during the exam.
You will be taken to a computer where the test administrator will make the sure the exam is loaded onto the computer and help you launch the exam. You will be presented with a series of questions you need to answer. There will be a timer (usually in the top right corner of the screen) that indicates how much time you have left. Some exams are broken into sections and you are given a fixed amount of time for each section, other exams give you a fixed amount of time to complete the entire exam. So don’t panic if you see a timer counting down 15 minutes when you are only on question one. Expect somewhere between 40-60 questions.
The questions generally follow a fairly standard format. First you are given a scenario “You are maintaining a SQL Server 2008 database on a Windows 2008 Server”, then you are given a problem statement “your project needs to store binary data and requires very fast update and retrieval capabilities” then you are asked the question “which solution best meets the needs of the team”. Watch for statements such as which solution “best” meets the needs of the team, that will help you narrow down which answer is correct, you can use VARBINARY, or Filestream to store binary data, but which is faster? remember the question stated the team required fast update and retrieval.
You may be asked to choose one correct answer, or two correct answers that together make a correct answer, or two correct answers each of which is correct on its own. Read carefully to make sure you understand what is being asked.
When you see a question you are unsure of, you can mark the question for review. After you have answered all the questions a summary screen is displayed and you can go back and review any questions. Personally I do not go back and review every question, instead as I go through the exam, I use the Mark for review option to help me remember which questions I wanted to go back and spend more time on if I had time.
Quick tip, if you have answered a question and the Next button is disabled, the system has not crashed, the exam is designed to force you to see all the answers before you can move on to the next question, so you may have to scroll down to see the the bottom of the last answer before the Next button is enabled.
After you have answered all the questions you will be given a chance to provide feedback on the questions and the testing center. After you submit the survey you will see a screen pop-up with your score, and at that point all you hope for is that magic 700! 700 is the passing score for a Microsoft certification exam, and just to clear up a common misconception, 700 does not equal 70%! Every exam goes through a beta testing process that helps Microsoft determine a reasonable passing score, so for an easy exam you might need better than 70%, for a tough exam you might need less than 70% to pass. There is a blog post on Born To Learn that explains this in more detail.
700 may not equal 70% but in my mind 700 = 1000. What do I mean? If you look at your transcript it will simply show that you passed the exam. I have passed exams with scores varying from 700 to 980, in the end all that really matters is you passed!
I have taken many certification exams over the years, so today’s Top 5 is all about Tips to help you when you take the exam, but this week you get double for your money a Top 10!
If you have taken an exam, I bet you have your own exam tips to share. So tell us your tips! and GOOD LUCK ON YOUR EXAM!
This blog post is also posted to Susan Ibach’s Blog
Come join me as I will be presenting this Friday in Ottawa on SQL Azure at CTE Solutions. I grew up on SQL Server, but it really isn’t that hard to get up to speed on SQL Azure. A lot of features are the same, but there are a few key differences you need to know. In this session I will be talking about what’s the same and what’s different. I’ll also provide some guidance on when to use SQL Azure vs SQL Server on premise solutions. If you can’t make it, check out my blog post summarizing the differences between on-premise SQL Server and SQL Azure. Looking forward to seeing you there! This session is part of CTE Solutions seminar day where you can attend a number of sessions on technologies taught at CTE Solutions. Registration is required by email email@example.com
Where: CTE Solutions, 11 Holland Avenue, Ottawa Ontario
When: Friday September 9th, 9:00-10:15 AM
What: SQL Azure
Why: Because SQL Azure is so easy to master, if you don’t know it you are missing out
Who: I will be doing the presenting, looking forward to seeing some of you there!
It’s fall and that means back to school for kids, it also means time to start up and really get rolling on new projects, which means it’s a good time to think about the big picture: Application Lifecycle Management (ALM). Developing an application is about much more than just writing code. You have to collect requirements, you have to determine which requirements you will develop in each phase or sprint, you have to develop the code, you have to test the code, you have to deploy the code, you have to support the code, you have to update the code and add additional features in each phase or sprint. This involves a lot of work and a lot of different people! You need a way to help the teams collaborate, you need a way to send feedback, you want to follow best practices, and ideally you don’t want all the teams using different tools! That’s where Application Lifecycle Management tools come into play.
Any application lifecycle management tool has to support the flow through the different phases of the projects and the flow from one team to another. The truth is most of us think of Visual Studio as a tool for editing and deploying code and nothing more, but that copy of Visual Studio installed on your PC right now may be capable of more than you realize. You may have an ALM tool sitting in front of you already. The best part is, it’s an ALM tool you already know. Yes, learning how to use the ALM features will take a bit of effort but many of the interfaces will be familiar and will integrate well with the code editor we know and love (btw - yes I do love the Visual studio code editor) . Visual Studio 2010 lets you track requirements, manage source control, and connects Quality Assurance (QA) with development and even supports the Eclipse development environment so its not just for .NET developers. Visual Studio vNext will take it even further with easier tracking and assignment of requirements in each sprint, a new code review feature, and more!
One of our goals this year is to help you understand the full potential of Visual Studio as an ALM tool and learn how to leverage that potential to help your next project. If you prefer self study you can check out the Visual Studio Roadmap, a great read with links to whitepapers and videos to help you understand all that Visual Studio has to offer as an ALM tool. If you prefer to have someone walk you through it, join us in a series of online webcasts over the course of the year. Starting with a series to help you make your testing more efficient. Today’s Top 5 is all about helping you get up to speed on testing features
5 Free Webinars to help you improve your testing
You definitely want to catch the first session featuring none other than Sam “ALM” Guckenheimer, Sam is the Product Planner at Microsoft for Visual Studio Team System and an authority on software testing and ALM. He literally wrote the book (Software Engineering with Microsoft Visual Studio Team System). This is an amazing opportunity to hear directly from the source. We are also bringing Sam to visit Canada as a keynote speaker at TesTrek in Toronto.
So register today for the Getting Agile with Testing seminar, September 21st with Sam “ALM” Guckenheimer and more great sessions to help you with your testing!
It struck me that if Dr Seuss was learning ALM from Sam “ALM” Guckenheimer it might have gone something like this:
That Sam A LM! That Sam A LM I do not like that Sam A LM Do you like your A L M? I do not like it, Sam A LM I do not like that A L M Would you like it here or there? I would not like it here or there I would not like it anywhere? Would you like them when you test? That is when they’re at their best! You should try it when you code It will keep you on the road When I test? is that best? When I code? Keep to road? I did not know this A L M I am not sure now SAM A LM Would you like it when you’re live? Your ops team will surely thrive. Would you like it to track need? Help your project to succeed! When I test that is best. When I code Stay to road. Sure to thrive when we’re live Help succeed meet user need! I did not know this A L M I am not sure now Sam A LM Would you like it when you plan know if deadlines meet you can? Would you like code source control It will help most every role When I test that is best. When I code Stay to road. Sure to thrive when we’re live Help succeed meet user need! When I Plan? Know I can? Code Control? Help each role? I did not know this A L M I am not sure now Sam A LM You do not like it, so you say Try it, try it, and you may Try it and you may, I say Sam! If you will let me be, I will try it, you will see… Say! I like this A L M I do! I Like it Sam A LM I will use it when I test I will use it that is best I will use it when I code That will keep us on the road I will use it when we’re live It will help my ops team thrive I will track each users need Then my project will succeed! I will use it when I plan! Deadlines meet, I know I can! I will do my source control It will help in every role! I will use it here and there I will use it EVERYWHERE! I do so like this A L M THANK YOU THANK YOU SAM A LM!
with apologies to to Dr. Theodor Seuss Geisel whose original Green Eggs and Ham is far superior to my attempts at prose!
(By the way I was going to have a verse that said Do you like them in a cubicle but i couldn’t find a rhyme)