March 6, 2015 at 4:39 pm
can anyone provide me real time scenarios where you build stored procedures, triggers, cursors? Also, I am confused on how the real time work happens.. i mean i have knowledge on all the concepts but i don't know how it happens in real time, in which scenarios you build which sql queries. Also, I heard that a company maintains test database for developers to work on, and a production database and that the test database contains some junk data and some real data. I am confused about this and i tried to google for articles but o luck. Can anyone suggest me good articles in helping me understand the real time work. Also, I would like to know what type of data issues generally arise when production server is being deployed that the db developer has to take care of?
March 6, 2015 at 5:31 pm
Your "real time" doesn't make any sense to me. Real time happens in SQL Server all the time. You INSERT data - it goes in in "real time". UPDATE/DELETE data - same thing. What are you looking for specifically?
Also, test databases are best if the actually mirror production data in pretty much every way: hardware, settings, data, etc. You need all of those to make sure your code will perform properly in production.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2015 at 7:52 pm
TheSQLGuru (3/6/2015)
Your "real time" doesn't make any sense to me. Real time happens in SQL Server all the time. You INSERT data - it goes in in "real time". UPDATE/DELETE data - same thing. What are you looking for specifically?Also, test databases are best if the actually mirror production data in pretty much every way: hardware, settings, data, etc. You need all of those to make sure your code will perform properly in production.
I'm pretty sure it was just bad english and he meant "real life" scenarios.
But I also have no idea where to start answering such a weird, broad, vague question. I don't really know how someone can know all the concepts without any idea of how a real life person would use any of them.
To be honest, it really just sounds like interview or test prep.
March 6, 2015 at 8:00 pm
Actually, what i meant was that I just have thereotical knowledge and I have been practicing queries on ssms installed in my laptop. I want to know how the stuff is done in real world, when you are working for a company. In which situations do you write stored procs, triggers and cursors? And some examples of real life scenarios where the production database faces some issues and the support people reach out to the database developers to help them and how to solve them.
March 6, 2015 at 8:25 pm
One "real time" scenario would be bank account transactions. You can now move money back and forth between your checking and savings accounts in "real time". It used to be that you would make a request and the money would move when they did the next nightly run. During the move, the funds were frozen and you couldn't get at them from the time you made the request until the move was completed that night.. or maybe the next night.
Similar with credit cards. They can figure out right away if you've reached your limit, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2015 at 9:00 pm
Thank you 🙂
March 6, 2015 at 9:03 pm
Jeff gave you smart example.
in addition- one more example.
"SQLServercentral.com" which you are using is one right now.
You have posted question. You are getting reply . that is real time ......example as well
March 7, 2015 at 2:50 pm
t.mounika01 (3/6/2015)
Actually, what i meant was that I just have thereotical knowledge and I have been practicing queries on ssms installed in my laptop. I want to know how the stuff is done in real world, when you are working for a company. In which situations do you write stored procs, triggers and cursors? And some examples of real life scenarios where the production database faces some issues and the support people reach out to the database developers to help them and how to solve them.
That is WAY WAY WAY WAY beyond a forum question!! Forums are for targeted needs and questions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 7, 2015 at 7:39 pm
t.mounika01 (3/6/2015)
Actually, what i meant was that I just have thereotical knowledge and I have been practicing queries on ssms installed in my laptop. I want to know how the stuff is done in real world, when you are working for a company. In which situations do you write stored procs, triggers and cursors? And some examples of real life scenarios where the production database faces some issues and the support people reach out to the database developers to help them and how to solve them.
In which situations do you write stored procs, triggers and cursors?
For Stored Procs, almost everything because they contain the language of the database and a well written stored proc can sometimes be made to beat the living tar out of ORM calls or Embedded SQL calls. It's also easier to deploy stored procs because you don't have to compile them and distribute the DLLs and it's certainly easier to debug them and troubleshoot them for performance.
Triggers should be infrequent. One of the more common reasons to used triggers is to populate audit tables with ANY changes made to data in a table. They also need to be written correctly but can save a huge amount of time instead of every app or stored procedure having to contain code to the audit tables. They also log sneaky humans trying to make direct changes.
As for cursors, they do have a place but it's a rare thing that any application code should actually need a cursor. DBAs find them useful for traversing many tables or databases but they should generally be avoided (like the plague) in application code or application related stored procedures.
And some examples of real life scenarios where the production database faces some issues and the support people reach out to the database developers to help them and how to solve them.
As Kevin suggests, that's an absolutely huge subject with many facets and any presentation of "real life scenarios" would be grossly inadequate for all the things that can happen out there. The key to success, though, is that, whether you're a front-end developer, a database developer, or a DBA, you have to make the realization that you're all working for the same company that pays everyone to make the company successful. I've seen too many times where there's a huge rift between developers and DBAs and there's just no good reason for it.
At my shop, I've virtually eliminated all of that. I sit with and work closely with the developers, they work with me, and we all work together on the common goal. More companies should do such a thing because they'd be a whole lot more successful if they did.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2015 at 11:50 pm
I was told by a senior DBA on my first day of working with SQL Server that creating stored procedures was the way to automating myself out the job. 😛
But seriously...
I have not used triggers because they are the death to my tables. I have not used cursors yet as well. I have however created and used many store procedures at work to make my life easier as well for automation of work.
For example, I was tasked to create an ETL System. I could use SSIS packages using SQL Server Business Intelligence Development Studio. I instead created this ETL System using all stored procedures to load data from a data source that was a .csv file into the database. ETL more specifically stands for Extracting, Transform and Loading of data.
Stored Procedures helped me get data from the .csv file, which was BULK INSERTED into a staging table where I cleansed and transformed the data. Once completed, I inserted data into it's final resting place where other users can query it.
Now I have those stored procedures assigned to jobs in SQL Server where they are executed in phases (or stages) every day. It's completed automated and easier to manage than the alternative such as SSIS packages. Stored procedures help me define my entire workflow for a very complex system that again, allows me to get one step closer to automating myself out the door. :hehe:
March 8, 2015 at 5:33 am
I sit with and work closely with the developers, they work with me, and we all work together on the common goal. More companies should do such a thing because they'd be a whole lot more successful if they did.
WHAT JEFF MODEN SAID!!!!!!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 9, 2015 at 3:39 am
t.mounika01 (3/6/2015)
can anyone provide me real time scenarios where you build stored procedures, triggers, cursors? Also, I am confused on how the real time work happens.. i mean i have knowledge on all the concepts but i don't know how it happens in real time, in which scenarios you build which sql queries. Also, I heard that a company maintains test database for developers to work on, and a production database and that the test database contains some junk data and some real data. I am confused about this and i tried to google for articles but o luck. Can anyone suggest me good articles in helping me understand the real time work. Also, I would like to know what type of data issues generally arise when production server is being deployed that the db developer has to take care of?
SPs:
Using Stored Procedures to Provide an Application’s Business-Logic Layer[/url]
How to Design, Build and Test a Dynamic Search Stored Procedure [/url]
Triggers: We'll leave this one for a future update.
CURSORs: Don't know because I've never had the need to write one of those things (can't recall the syntax either).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 9, 2015 at 6:58 am
t.mounika01 (3/6/2015)
Actually, what i meant was that I just have thereotical knowledge and I have been practicing queries on ssms installed in my laptop. I want to know how the stuff is done in real world, when you are working for a company. In which situations do you write stored procs, triggers and cursors? And some examples of real life scenarios where the production database faces some issues and the support people reach out to the database developers to help them and how to solve them.
Best way to learn this stuff is by doing. You can get a copy of SQL Express for free to experiment with. But the best thing to do is get a copy of the Development version of SQL Server. That costs about $50.
To understand where each object is used in the database, the best way to go about it is to understand what each object does, what it's for, how it's defined. That will tell you more than anything about where to use each one. The Books Online are your single best resource. After that, you're in the right place to get answers to specific questions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2015 at 11:33 am
t.mounika01 (3/6/2015)
Actually, what i meant was that I just have thereotical knowledge and I have been practicing queries on ssms installed in my laptop. I want to know how the stuff is done in real world, when you are working for a company. In which situations do you write stored procs, triggers and cursors? And some examples of real life scenarios where the production database faces some issues and the support people reach out to the database developers to help them and how to solve them.
I'd suggest installing and studying the Adventureworks database, it might shed some light on a lot of the issues that you want to learn about. It's a very good representative example of a real world database with a lot of sophistication built in to it.
As a very broad rule (one of the key sayings here on SSC is 'there's several ways of doing that') we use triggers to enforce relationships when the normal foreign key declarations (DRI/declared referential integrity) don't work, also for doing additional processing, such as change audits.
The conventional thought on cursors is to not use them in production as they're bad technique: they break set-oriented operations and basically violate any of the performance gains that you can get through properly normalized designs. That said, they can be useful for some administrative tasks. Take a look at Jeff Moden's articles here on avoiding RBAR, or accessing a table Row By Agonizing Row.
Stored procedures can do any number of things, thinking back over the years I think I'd describe a proc as code that needs to run to provide processing outside of the normal user insert/update/delete process. The student management system that I'm working on right now has two procs, one for promoting students to the next grade and one for resetting a qualification field, both of which are three update statements (early design, they'll get more complicated). We also have CLR as an additional tool, but I'm not very familiar with that. There's a Stairway To CLR series here on SSC that you might look at.
I would recommend picking up one of these books, both by Microsoft Press: SQL Server 2008 Database Development Training Kit, or SQL Server 2012 Step By Step. You can get the first one for $2 + shipping from Amazon, and the code in it is more than good enough to get a firm grasp on what T-SQL is.
Good luck!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 9, 2015 at 1:02 pm
Do you have a sample you would be willing to share? (Stored proc working with csv?) I do SSIS CSV imports all the time!! I didn't know i could avoid that entirely ...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply