MS SQL Server - A Plethora Of Limitations...
Jul 20, 2005
Why is it, Microsoft manage to write operating systems and office
applications with every bell and whistle facility known to man. Yet,
even after years and years of development they still cannot produce a
decent version of MS SQL Server, one without a plethora of flaws and
limitations? Personally I'd sack the MS SQL Server Chief Architect,
start addressing some fundementals and do what MS do best - copy the
best functionality of their competitors.
Here's a few reasons why I feel MS SQL Server is still a load of crap,
and will never be many developers RDBMS of choice :
1. Can't use domains in Multi-statement table-valued function table
definition
2. No concept of table.column.datatype MYTABLE.MYCOLUMN%TYPE for
function / proc parameters, variables. Schema evolution will require
changes to dep functions / procs.
3. Cannot call non extended user defined procedure from within stored
proc - prevents code re-use
4. No create or replace for functions / procs. Changes to procs
require either a change from create to alter, or drop then create
5. Functions must return result on final statement - requires
excessive use of local variables. Why!!!???
6. No user specifiable date format routines - must use convert with
limited options
7. No treewalk mechanism, not easy to traverse hierarchical structure
8. Calling procedures with out parameters require user to also
specifiy which are out params
9. Stored Proc recursion limitations to only 32 nested levels, rather
than monitoring by stack size.
10. No on delete set null referential integrity, this is fundemental
in relational databases, has to be done by trigger. Yuk.
11. No partitioning on db tables
12. Cannot raiseerror in stored functions. No way to inform user of
incorrect parameters etc.
13. No facility for sub - procs / funcs - for localised code
normalisation.
14. No function overloading
15. No concept of grouping similar procs / functions into a collective
package - can mean 1000s of procs rather than a handful of packages.
16. Non standard func / proc calling - proc non parenthesised but,
function defined and called with parenthesis
17. Limited column name length in table variables / Multi-statement
table-valued function table definitions
18. No intersect, minus operators to complement union and union all
19. No exception handling - need I say more about this fundemental
requirement?
20. No handling for select statements using proc parameters passed in
as null e.g.
create procedure sp_test.......
......
select @MyID = ID
from mytable
where parent_id = @parent_id.
If @parent_id is null, select will not return anything even if values
for null parent_id exist.
21 No boolean type for use in stored procs / funcs. SQL standards do
allow booleans (null, FALSE, TRUE)
22 No repeat until - (post predicate loops), while loops can perform
this but one extra check often has to be performed
23 No equivalent to cursor or table %ROWTYPE, when fetching from a
many column cursor, all bind variables have to be declared
individually
24. Declarative Delete cascade in SQL Server is at best rubbish - e.g.
No table can appear more than once in the list of all cascading
referential actions that result from the DELETE or UPDATE. Almost all
other RDBMS I've tried seems to manage it.
Create Table ForKey
(
MyID int not null identity primary key,
MyText varchar(30)
)
go
Create Table Refs
(
SomeID int not null primary key,
RefID1 int not null foreign key references ForKey(MyID) on delete
cascade,
RefID2 int not null foreign key references ForKey(MyID) on delete
cascade
)
Server: Msg 1785, Level 16, State 1, Line 2
Introducing FOREIGN KEY constraint 'FK__Refs__RefID2__1BFD2C07' on
table 'Refs' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or
modify other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
25. Domains cannot be altered without dropping all dependencies
(stored procs / funcs) even if type isn't changed but say scale and
precision- this is ridiculous. What is the point of domains?
26. No "for loop", while loops can perform this, but resultant code is
not as clear or tidy
27. Can't use CURDATE() inside stored procs / funcs. How crap is
that?
Don't try and make excuses for MS as I have hundreds, anyone else have
any?
Jeager
View 13 Replies
ADVERTISEMENT
Jul 9, 2006
Hi,
As SQL Server XE can run on one PC maximum, does it mean that it can't support multi users??? I mean could it be installed on one server and to be used by multiple users connected to server with their PCs?
thanks in advance
View 8 Replies
View Related
Aug 31, 2006
what are the limitations in MS SQL Server Express (for a network application)?
View 1 Replies
View Related
Jun 21, 2006
I was looking around, and I found this info on SQL Server Express:The 4 GB database size limit applies only to data files and not to log
files. However, there are no limits to the number of databases that can be
attached to the server.Does this mean I can use several different databases in my website, like one separate db containing forum entries, and one with user details and related stuff?
View 4 Replies
View Related
Oct 12, 2000
We are primarily a DB2 and Oracle shop. There are movements towards SQL Server for smaller applications. We would like to know if anyone out there has shop standards in determining which database should go with what type of application. What we are looking for are possibly database sizes, number of concurrent users, hardware and software cost, network infrastructure, security, scalability, and development tools. Any information would be appreciated.
View 1 Replies
View Related
Jul 20, 2005
Hi all,I'm looking for some ball park estimates on when SQL Server might startto break down, and can't find any reliable info. Any insight is appreciated.Basically, the situation is this: The database structure is very simple;just one table with about 15 columns and about 60-75 million rows. There'sno need for massaging data or complex relations, just simple searches onmaybe a max of 5 columns. Out of the gates we'll be looking at having 30concurrent users and rapidly expanding to about 300-400 concurrent users.I might need to rebuild the database on a daily or weekly basisdepending on how often changes are made to a master file. In the past I'vebeen bit in the butt with the absolute crappiness of SQL Server'sreplication, so I'm going to try to avoid that path if I can (plus I alreadyhave some scripts written to delete and rebuild a similar database on anightly basis). Would it be practical to destroy and rebuild a database thissize on a daily basis?The big question is if searching 60-75 million records is practical inSQL server. If so, what kind of machine would I need to get a nearly instantresponse time per search (.2 second or so) when everyone's banging on it atonce? How many concurrent users can I expect to be able to practicallysupport before SQL Server will start to bog down? Thanks for your thoughts,-Ringo
View 3 Replies
View Related
Feb 1, 2005
Whats the limitations of data storage in sqlserver DB. How will be the perforamcne if i have database which will get a data of 400 GB per year and all the data should be there in the table the whole year and then it can be archived.
View 2 Replies
View Related
Apr 24, 2008
Hello everybody
My office gave me assignment to find out the scope & limitations of the two Microsoft database applications - MS Access and MS SQL Server. After the findings, we have to chose the more appropriate database for our new project, which will have a humble start but may grow big in a year or two.
I will apreciate the info in facts & figures (not only in general).
View 7 Replies
View Related
Mar 13, 2007
Hi,
I am currently using the SQL Server Express edition with the intention of upgrading to the full version in the future.
I know the size limitation on the Database is 4GB - however does this include the log file as well? Nothing I read says the log file is included however if anyone can give the definitive answer I would be grateful.
Also my log file maximum is set to 2GB what issues are there with reducing this to say 500MB? I assume SQL Server automatically drops the old log data to add new data when the maximum is reached.
Thanks for your help.
Andrew
View 3 Replies
View Related
Dec 7, 2005
I have a Web site that has a database backend, which stores user information. I would like to encrypt the user information in a granular way.
View 16 Replies
View Related
Aug 13, 2007
I've created a database design and I'm trying to figure out if it's workable or not. I have 2 tables in particular that are sparse (20 columns, but only a few have data - about 100 bytes/row) but will grow very large - to the tune of 700 million rows.
My question is whether or not there is a problem with SQL Server 2005 having 1.5+ billion rows of data even though it will likely only take up 100 gigs or so on disk. Anyone have experience in large numbers of rows like this? We're still doing testing as far determining how slow queries will get.
Thanks,
Craig
View 3 Replies
View Related
Oct 22, 2006
Hi, I'm not sure if this is where I should post this, but I'm trying to find out what the limitations of SQL Server 2005 Developer Edition (DE) are. I've gone through the Microsoft SQL Server site and they have very little information about the developer edition.Is there a connection limit for DE like 2000 did?
View 3 Replies
View Related
Nov 20, 2007
Dear,
I'm building an ASP.NET application using C# language and SQL Server 2000. This application is dedicated to be hosted on a VIsta Machine.Unfortunately Vista does not supports SQL SERVER 2000 so i must migrate to the SQL SERVER 2005. Since the application is a small application, we couldn't afford buying an SQL SERVER 2005 License so i decided to use SQL SERVER 2005 Express Edition. But i would like to know exactly what are the Limitation on the Express Edition to see if it will work with my application or No. Please Can anyone refer any document that describes in details all the limitation of the Express Edition ??? Thanks a lot.
View 1 Replies
View Related
Dec 20, 2007
Hi,
Apologies for the generic subject heading but I have a client who at my suggestion has implemented a reporting solution using SSRS. However, we are now finding serious limitations with this solution, primarily regarding exporting matrix data to Excel. The major concern is that after exporting matrix data you cannot sort, filter or format properly as we cannot repeat the group data, merged cells is also another big problem.
They are now seriously looking at bringing in different technology to address these problems and I was wondering if anybody had any positive ideas, as to how I can provide a way forward and overcome our issues?
Can I easily add an add-in to the existing export options, does SQL 2008 offer any improvements, are there any 3rd party offerings out there?
Grateful for any advice.
View 3 Replies
View Related
Apr 23, 2008
1) SSRS export to Excel functionallity has an important limitation... is terribly slow when exporting huge amount of data to Excel.
2) Subreport are not supported to export in excel. This is some known issue and limitations of SSRS.
View 1 Replies
View Related
Apr 7, 2007
I have software that uses SQL Server Express as it's database. I am only able to import so many records until it stops and fails to allow me to import anymore.
I'm very new at this but, is there some type of limitation on Table size that's preventing me from importing anymore data?
I'd really appreciate it if someone could help me,
Thanks... Bill
View 10 Replies
View Related
Feb 2, 2015
I'm having an issue in encrypting large documents. I know that previous editions ENCRYPTBYKEY had a maximum size of 8,000 byte limitation. Does SQL Server 2014 have any new features that overcomes this limitation?
Using
SQL Server 2014 Std
Symmetric key with AES_256
View 2 Replies
View Related
Feb 27, 2006
I'm new to SQL so don't laugh if this is easy to spot but I'm having trouble with a select statement but I do not think it's the syntax etc, I'm convinced it must be due to some restrictions and limitations within SQL.
Could anyone shed some light? I've been 'googling' for two days now and can't find anything, much appreciated! thanks.
SELECT a.medno_id,
tblAction.action_shortdesc,
tblClassification.class_shortdesc,
tblType.type_shortdesc,
tblUnit.unit_shortdesc,
tblSystem.system_shortdesc,
a.med_oldserial,
a.med_builddate,
tblLibrary.lib_shortdesc,
a.med_f24bookno,
a.med_f24entry,
a.med_f102bookno,
a.med_f102entry,
tblTransport.trans_shortdesc,
a.med_courierno,
tblPOC.poc_surname,
a.med_title,
a.med_notes
FROM tblMedia a
INNER JOIN tblAction ON a.action_id = tblAction.action_id
INNER JOIN tblClassification ON a.class_id = tblClassification.class_id
INNER JOIN tblType ON a.type_id = tblType.type_id
INNER JOIN tblUnit ON a.unit_id = tblUnit.unit_id
INNER JOIN tblSystem ON a.system_id = tblSystem.system_id
INNER JOIN tblLibrary ON a.lib_id = tblLibrary.lib_id
INNER JOIN tblTransport ON a.trans_id = tblTransport.trans_id
INNER JOIN tblPOC ON a.poc_id = tblPOC.poc_id
WHERE a.medno_id = 327
ORDER BY a.med_effdate
I've spaced out the query so it's easy to read
I've started the query from scratch building my SQL adding one field at a time and it works perfect until it reaches a certain number of fields, it's as if SQL has a limit to no of fields it can return/be used in the select part of the statement. Are there any limitations I should know about? or am I being an idiot and doing something I shouldn't?
Hope this makes sense, thanks!
View 3 Replies
View Related
May 11, 2006
What are the limitations of the SQL 2005 Express Management Studio tools, as opposed to the full blown 2k5 Tools (i'm not referring to the Server, just the tools). I ask because the install for the 2005 Management Studio Tools alone is 878 megs.
View 1 Replies
View Related
Feb 27, 2008
Hi.
I've been using CE 3.1 in a .NET/C# application. My knowledge of database technology is very basic, and I'm wondering how far I can take my application with CE, given its limitations, which I stumbled across when I read the following about VistaDB: http://www.vistadb.net/compare_sql_compact.asp
For right now, my application uses a CE database as a fancy log file. There are only two tables in the database -- one to hold the log information, and one to hold information about different "runs" of my application. The only time a row is ever updated is when a row in the latter table is updated (once) to indicate that that "run" has completed. Other than that, I only add and delete rows.
Right now, two different processes on the same CPU (the same CPU as the database) write rows to the database during a run of my application. They do this by accessing a singleton object, hosted in a Windows service, via .NET remoting. One of these processes also polls the database continually to read it for display purposes.
So. My questions are:
I can live with the single-user limitation, given my application. But what exactly is it about using CE that limits me to a single user? I never actually specify user information, as far as I know. Am I missing it?
Can I assume that the "single CPU supported" limitation simply means that it only runs on a 32-bit Windows machine, and that it has nothing to do with multiple databases running on different CPUs at runtime?
Why is it that the two processes in my application seem to be able to connect to my database concurrently, when this is apparently a limitation?
As I said, my knowledge of this technology is quite elemental. Forgive me. But I figure I'm in the right place to change that. :-)
Thanks.
Mike
View 1 Replies
View Related
Nov 15, 2007
I am receiving an error message while using the System.Transactions.TransactionScope class. The error message that I am receiving is "Communication with the underlying transaction manager has failed". This error seems to only appear when I have my web application one server, Server1, and my database on a second, Server2. When I run the web app on the same server as the database (i.e., web site and database on Server2), I don't receive this error. So, this leads me to believe this has something to do with MS DTC. Is there a limit to how much data MS DTC can manage for a given transaction? If so, is it configurable? When I run my code, the application fails after a certain number of steps (this is repeatable). See sample code below. When I execute the code below, the error occurs on UpdateBody2();. If I comment out UpdateBody2(), the error will now occur on UpdateBody3();, and so on. This leads me to believe that I have hit some upper limit. My code follows a pattern similar to this:using {TransactionScope scope = new TransactionScope()){ UpdateHeader(); UpdateBody1(); UpdateBody2(); UpdateBody3(); UpdateFooter();}Where each of the classes follows a pattern of:UpdateHeader(){ using (SqlConnection conn = new SqlConnection()) { conn.Open(); // Do something conn.Close(); } } Environment:ASP.NET 2.0SQL/2005 StandardWindows Server 2003 Thanks.Steve
View 3 Replies
View Related
Jun 22, 2004
I'd like to use MSDE since it's free instead of SQL Server for my database. I will be hosting a portal type site. If all goes well and my site is wildly successful are there any limitations in MSDE that I need to worry about?
For instance, I thought there was a limit to the number of connections. I thought I remember seeing 5 or 50 on the microsoft download site.
If there is a connection limit of say 10, what happens when connection 11 comes in? Does it just wait for a free connection or does it fail?
Is anyone using is as the database on a large portal site? How many users are there in total? How many connected at the same time?
I am under the assumption that it is SQL Server underneath, so I assume the performance and abilities are very good. Is this a fair assumption?
Thanks for the input
View 5 Replies
View Related
Jun 6, 2006
Hi, I was curious if anyone knew if there was a way to get around SQL Express Edition 2005's limitations to support remote connections. If I install SQL Server 2005, will there be a smooth transition between the two on my IIS 5.1 server? My database is extremely simple and only consists of a single table without any complex queries. Idealy, I would like to spend no money since I am a poor collge student doing this as a project. Suggestions?
View 9 Replies
View Related
Jan 20, 1999
Does anyone know the limitations of access in records?
CZ
View 1 Replies
View Related
May 18, 2004
I updated a varchar field fom 500 length to 800 and now nothing works. Are there limitations to how big a varchar table entry can be and if so what is the alternative?
Cheers
View 2 Replies
View Related
Aug 2, 2006
Hi all there,I'm a newbee to this forum. I've a question, is there any limitationon "IN" clause in select query.for example :"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN('EMP1001','EMP1002','EMP1003', etc, etc, so on)".I've read at some documentation that there is a limitation for Columnsin a table and i.e. we can have only 1024 columns per table, is thistrue?Plz help me !!Thanx in advance.Kind Regards,Harry
View 4 Replies
View Related
Nov 29, 2006
Hi,
What are the limitations of using the automated conversion tool and how to deal with compatability issues?
Thanks
View 2 Replies
View Related
Dec 12, 2007
Is it possible someone could let me know the exact differences between the SSMS Express Edition and SSMS Standard Edition?
Just simply what you cannot do in the Express edition but can in the full edition.
Thanks
View 7 Replies
View Related
Oct 3, 2006
Hi,
I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.
With the FTP task could i get a list of directories on the FTP server, and compare the folders with a result set returned from a SQL query. If any of the directories on the FTP site then i want to download each of the relevent directories and there contents to the local machine.
Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?
Many thanks,
Grant
View 7 Replies
View Related
May 12, 2006
I had a question about the CPU limitations of SQL express. It says limited to 1 CPU but what if I have multiple instaces since the application is a new service and runs in it 'sown memory space how is it limited to 1 CPU? does the service use multiple processors and the queris are binded to one?
Thanks
View 4 Replies
View Related
Mar 10, 2008
With the limitation of 1Gb of ram and 1 CPU for SQL Express, does that mean we cannot load it on a server with multiple CPU's and more ram, or that we can load it and it will only use 1CPU and 1Gb of ram?
View 7 Replies
View Related
Feb 10, 2006
can sqlExpress handle 200 users all at the same time?
View 1 Replies
View Related
Oct 9, 2007
Can anyone tell me if SQL 2000 has a record limitation on tables?
The table that I have is very basic. It contains 6 columns, none of which can contain more than 10 characters, but I'm already sitting at 2,500,000 records and expecting up to 20-30 million records. Can it handle it?
Thanks in advance.
View 2 Replies
View Related