Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / SQL Server - wooah!

Author
Message
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 29th Apr 2004 00:18
Link
I've used SQL Server 2000 for about a year now, but only as a dumping ground for data. That's mainly because the product in question is cross-platform, and SQL server just happens to be the database of choice for Windows.

Been doing an SQL programming course this week (the Microsoft one) and now I realise...you just shouldn't use SQL server unless you have done the training! I never realised just how different it is to any other platform, and just how much knowledge of the internal gubbings you need to understand to make it tick.

I always thought that an SQL administrator job was "jobs for the boys". Now I know...SQL Server needs a 24/7 babysitter.

BatVink
http://facepaint.me.uk/catalog/default.php AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
Toby Quan
21
Years of Service
User Offline
Joined: 16th Oct 2003
Location: U S A
Posted: 29th Apr 2004 00:21
Link
I am a SQL Server DBA, and I love the tool. It's relatively easy to implement, and its very powerful. And affordable!!!

I've never used any other Server database.
Back to top
Profile PM Email Website
CattleRustler
Retired Moderator
21
Years of Service
User Offline
Joined: 8th Aug 2003
Location: case modding at overclock.net
Posted: 29th Apr 2004 00:38
Link
I do, and have done, a lot of programming for use with SQL Server - I dig it but it's not the type of programming that makes me tick. My partner on the other hand is an all out guru (in sql, access, oracle and vb6) - so whenever we have a project involving sql db he handles the db and the SProcs, and I handle the vb/vb.net portions... lucky me


Home of the VB.NET Class Builder Utility - Demo and FULL versions now available!!
Back to top
Profile PM
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 29th Apr 2004 01:56
Link
One thing I've discovered - and it surprised me - is that writing SQL statements in VB (or any other front end) is the worst thing you can do. I've done this for years, and now I've come to discover that Stored Procedures can be thousands of times faster...scary!

The problem is that coders, like myself, tend to work sequentially, even with db updates. SQL Server is optimised for Set updates...update 1,000 records, then commit them instantaneously. As a midrange platform coder I don't like this, it goes against the grain. But then, that'll be why I've learned so much in the last 3 days!

BatVink
http://facepaint.me.uk/catalog/default.php AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
Richard Davey
Retired Moderator
22
Years of Service
User Offline
Joined: 30th Apr 2002
Location: On the Jupiter Probe
Posted: 29th Apr 2004 02:25
Link
SQL Server (like most Microsoft products) has one flaw with it - that of user perception! Just because it has a nice GUI and is relatively easy to get into it, people assume it's plain sailing and "not that complicated" Oh how wrong they are.

My last .NET project had thousands of lines of stored procedures code with full transaction handling across a server cluster, and I laugh in the face of those who claim it's all "easy"

Cheers,

Rich

With our species on the edge of extermination,
with no prospect but a horrible death,
we actually played games.
Back to top
Profile PM Email Website
CattleRustler
Retired Moderator
21
Years of Service
User Offline
Joined: 8th Aug 2003
Location: case modding at overclock.net
Posted: 29th Apr 2004 04:51
Link
not easy at all

but to respond to BV, yes luckily I learned early on that stored procedures are you best friend when it comes programming against a db. I mean as programmers we have enough code to write, not to muck it all up with inline sql from within vb code -yuck! The only (and I mean only) inline sql I will ever put in my code is a simple:

SELECT * FROM tbl_wherever

that's it. anything else ------> Stored Procedure


Home of the VB.NET Class Builder Utility - Demo and FULL versions now available!!
Back to top
Profile PM
Toby Quan
21
Years of Service
User Offline
Joined: 16th Oct 2003
Location: U S A
Posted: 29th Apr 2004 08:37
Link
Hmmm....

Most of my VB6 programs have TONS of inline SQL Statements in them. They are all built at run-time.

There are many times when you just need to build dynamic SQL Statements. Like for instance:

The user needs to run a report. And they want 4 different ways to run it (common in my workplace).

They might want to filter it by one thing, and the might want to filter it by another. With these things, creating the SQL at run-time works very well.

I love SQL, and I use it religiously. But I also see a need for Stored Procedures as well!

There is a time and a place for everything. And right now, it's time for me to go and watch Conan O'Brien!

Later, yo.
Back to top
Profile PM Email Website
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 29th Apr 2004 11:04
Link
Quote: "The user needs to run a report. And they want 4 different ways to run it (common in my workplace)."


You should still use a Stored Procedure for this, 2 reasons (that I kniow of)...

1. They are pre-compiled, so the optimisation is only done once (in most instances). Filtering is still using the same methodology each time, just on different records, particularly if your data is evenly distributed.

2. Once cached, all users running the same report can use the cached Stored Procedure.

Stored Procedures can (and usually are) parameterised, so selection criteria is not an issue.

User reports are actually a good example of when not to use inline SQL. Yes, I had the same preconceived misconception too. Generally, you are handling larger amounts of data, and this is where Set theory excels.

See, 3 days down and I already think I'm an expert. I'll have forgotten it all by next week

BatVink
http://facepaint.me.uk/catalog/default.php AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
adr
21
Years of Service
User Offline
Joined: 21st May 2003
Location: Job Centre
Posted: 29th Apr 2004 12:14
Link
Quote: "Now I know...SQL Server needs a 24/7 babysitter."

Which is why we use MySQL

stop();
hammertime();
Back to top
Profile PM Website
CattleRustler
Retired Moderator
21
Years of Service
User Offline
Joined: 8th Aug 2003
Location: case modding at overclock.net
Posted: 29th Apr 2004 16:20
Link
BV, exactly


Home of the VB.NET Class Builder Utility - Demo and FULL versions now available!!
Back to top
Profile PM
Toby Quan
21
Years of Service
User Offline
Joined: 16th Oct 2003
Location: U S A
Posted: 29th Apr 2004 17:54
Link
Well, inline SQL seems to work very well for me. And since the users make changes (change their minds about what they want) quite frequently, it sure is nice to only have to change the program in one place.

For instance, when they want a new field on the report, I don't have to change the stored procedure, and then change the report. I just have to change the report.

If I made a stored procedure for every report I have written for our case management system, then there would be 200 stored procedures on our server. And some of them would be for such ad-hoc things that people just needed once. It might get out of hand.

But to each, his own. You have to follow your heart.
Back to top
Profile PM Email Website
OSX Using Happy Dude
21
Years of Service
User Offline
Joined: 21st Aug 2003
Location: At home
Posted: 29th Apr 2004 18:00
Link
SQLite is pretty good, although, of course, its not really used for a many client system.
Plus, I've got a plug-in to interface to it...


The place for all great plug-ins.
The Coding Area - From my brain to your browser...
Back to top
Profile PM Email Website
spooky
22
Years of Service
User Offline
Joined: 30th Aug 2002
Location: United Kingdom
Posted: 29th Apr 2004 19:21
Link
Just done a count on how many different queries are in my main work software package that I wrote about 80% of (accounting and inventory system), and the answer is 4766 and some of these are massive queries collating data from 10 or so tables at once. I love writing SQL queries!

Boo!
Back to top
Profile PM Website
CattleRustler
Retired Moderator
21
Years of Service
User Offline
Joined: 8th Aug 2003
Location: case modding at overclock.net
Posted: 29th Apr 2004 19:30
Link
spookster-I'll keep that in mind


Home of the VB.NET Class Builder Utility - Demo and FULL versions now available!!
Back to top
Profile PM
Back to top

Login to post a reply

Server time is: 2024-11-25 05:32:25
Your offset time is: 2024-11-25 05:32:25