locked
Lite Learning of SQL RRS feed

  • Question

  • Good day,  I'm hoping to find out where I can get some lite learning on SQL.   I'm working with Sharepoint and have somewhat of an understanding of SQL.   I posted to the SharePoint forum on retrieving a large amount of records from SQL to display in a webpart.  It was recommended that I pull a subset of information.    I think this means that I should create some separate tables in SQL containing only the information needed for the different types of views that I would like to display.   So how do I create a query for these subsets of information in SQL?  Also,  I would like a better understanding of SQL,  but don't intend on being a DBA so I'm looking for some lite learning on the subject... Any ideas?

    One other thing I wanted to add was the term  data locking in SQL,  can you please explain this to me?  Thanks Judy

    Thanks Judy
    Wednesday, July 30, 2008 5:40 PM

Answers

  • Hi Judy,

     

    You've mis-interpreted the recomendation from the ShartPoint forum a little bit; they were not suggesting that you create separate tables with only the data you want to show in your web part, just that you ensure you're query is specific to the data you want to show rather than returning a whole bunch of records and trying to filter them later. The reason to do this is that SQL Server is better at filtering the data directly in the server and returning your records than the program would be at filtering them after the data is returned. It also reduces the amount of information that has to be returned across the network, which makes the query faster.

     

    As far as an easy source for learning some SQL, there are a few places you can look. There is a video series that gives the basics of using SQL Server, you can find it at http://msdn.microsoft.com/en-us/beginner/aa718391.aspx. If you are using SQL Express and you register it (you'll find a Register button on the page I've linked to above) you will also have the opportunity to download an e-book as a registration benefit - the e-book has some information about writing simple queries.

     

    Data locking is a fairly complex topic to cover in a forum post (there are whole books on it) and for basic usage you probably don't need to know too much about it since SQL does it for you automatically. The jist of locking is that only one person/application/process should be able to change data at a time to ensure that the data remains consistent. Think of it like this, say that Bob's Books sells books online. The only have one copy of Harry Potter and the Sorcerers Stone in stock and it just so happens that you and I both want to buy it. We go online at the same time, put the book in our cart and hit the purchase button. Without locking it would be possible for both of our purchases to be processed, meaning we both pay for the single copy of the book. Luck of the draw is that the book gets shipped to you and I just get a nice charge to my credit card and no book to show for it. Bob's going to be hearing from me and it's not going to be a happy conversation. With locking, only one of our transactions can be processed at a time; let's say your transaction goes in first - the record for the book is locked while your transaction processes so when my transaction attempts to modify the books record (i.e. by decrementing the inventory count) it can't get a lock, so my transaction blocks until you're finished and the lock is released. When you're finished, I can now proceed, but my transaction sees the inventory count is 0, so my purchase fails. I still don't have a book, but at least I didn't pay for it.

     

    There is way more complexity in locking, and many reasons and ways data is locked, but that should give you the general idea about what it is and why it's important.

     

    Regards,

    Mike

     

    • Marked as answer by Josnoe Monday, July 27, 2009 12:43 PM
    Tuesday, August 5, 2008 3:34 AM