|
I'm surprised, that the COLLATION used in a DB Create Statement does have influence in executing SQL statements. Following I testet:
A. Insensitive: Creating a DB explicitely "Case Insensitive"
CREATE DATABASE CIDB COLLATE Latin1_General_CI_AI;
GO
USE CIDB;
CREATE TABLE TEST (ID INTEGER);
SELECT * FROM TEST;
select * FROM TEST;
SELECT * FROM Test;
select * FROM Test;
:) --> Everything works like expected, MSSQL does not bother about "TEST" or "Test".
B. Sensitive: Creating a DB explicitely "Case Sensitive"
CREATE DATABASE CSDB COLLATE Latin1_General_CS_AI;
GO
USE CSDB;
CREATE TABLE TEST (ID INTEGER);
SELECT * FROM TEST;
Everyting up to here is working fine. But ...
SELECT * FROM Test;
??--> fails with error message "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Test'."
... obviously because the database is created "Sensitive".
What's really surprising for me, that the collation sequence now also
makes the SQL Language a case sensitive, not the language it self but
the objects (table-, field- names and I assume also constraint names).
Am I the only one who is surprised by this?
Thank you in advance for some high lighting comments.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
It might seem odd at first glance, but it makes sense when you consider the fact that the names of all the objects within the database are stored in system tables within the database itself.
If the default collation for the database is case-sensitive, then the collation for all of the system tables is case-sensitive. So SELECT * FROM sys.tables WHERE name = 'Test' isn't going to match a row where name = 'TEST'.
Whilst Microsoft could have chosen to make the system tables always case-insensitive, that would have been a breaking change. Earlier versions of SQL Server didn't allow you to mix collations within a single database, so introducing that change in a later version could have broken existing databases or scripts.
There are effectively two "solutions":
- Always create your databases using a case-insensitive collation, and use a case-sensitive collation on individual columns;
- Always write your scripts using the case of the table and columns names as defined when they were created;
SQL Server Case Sensitive Collations and DMVs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Wow, great! Thank you for your speedy Response.
I think I will go with "1.". Little bit more work, but from my Point of view the most transparent way.
Thank you again.
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Quote: that would have been a breaking change
After thinking again and again about it (I'm an old man and need therefor more time), is it not MS who is breaking the SQL Standard with this?
Not a big thing, only a thought.
Finally, it is like it is (with MSQL) and I have to live with it.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
0x01AA wrote: is it not MS who is breaking the SQL Standard with this?
Yes. It looks like SQL92 says that object names should be case-insensitive unless they are quoted.
I guess they decided the dangers of making the change outweighed the inconvenience of not following the standards.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks again to give me some confidence on this
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Hello experts
I’m usually organizing the data internally using a “system” generated id (at the moment int generated by mssql sequences) to define primary keys and foreign key relations. That has sometimes advantages but on the other hand it is not really the clean way from the point of theory (and for data exchange it can be the real pain).
Why?
The user usually needs also to have his “user-id” which is the user’s primary… so with the way above I introduce an extra index. And basically the user primary should enough and should be used to define the db constraints. But programmers are lazy and like keys all of the same type….
Ok, long speech. What I’m really like asking for Best Praxis is:
I’m at a point to migrate Interbase databases to MSSQL. Interbase has as default case sensitive indices while MSSQL has case insensitive as default.
In case this “user-id” allows alphanumeric, what is better/best Praxis/usual:
a.) Make Ids case sensitive ?
b.) Make Ids case insensitive?
Thank you in advance
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Since you can do "like" and "sounds like" searches in SQL Server, you need to review your "requirements" more.
For the record, the recommended approach for "entity keys" are "nonsense numbers"; i.e. identities / generated id's. And they're central to Entity Framework's "tracking" ability.
It's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email; in which "case", you can case it the way you want when comparing.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Thank you, I need to think more about
Quote: 's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email.
Thanks again
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I find that the more systems I'm integrating with, the more I rely on creating "API's" where the object "getters" do some of the translating on the fly for a better or cleaner "view" within the context of the application. Molding reality.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Store all in upper-case and go for insensitive. Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
For primary keys, I still recommend normalization. Your auto-generated identity is of little use to the user (and should not be visible).
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Quote: Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
That is exactly the Point for which I'm looking for. Completely agree with you. Thank you for confirming this.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
You're welcome 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I want to ask how Facebook database works like? For example if I consider only the FB posts that users put on this timeline, how that post table would work?
Some statistics.
1. There would be more than 1 million posts every second.
2. Other users may be commenting on that posts too.
Questions.
1. Is there only 1 post table or multiple posts table that contains user post based on their geographic location. This may help in reducing the load from a single post table.
2. Is Fb database has some different approach then the databases of normal (low traffic) website?
3. Every database whether Oracle or SQL server has a limit in terms of number of concurrent connection, no of inserts queries performing per second and so on. So how FB is doing this?
Hope my questions make sense. Anyone who might be in FB database team might help me in my questions?
thanks
|
|
|
|
|
|
i need help with my new database program that has to do with Promotion of Staff to a new level. The database have three (3) tables: namely
1. <b>Promoted List table</b>, which have the following columns; (Id No., File No., Name(Staff name), Date of Birth, Date of First Appointment, Date of Present Appointment, New Level, Old Level, State Office location, Branch Office, State of origin and Year of Promotion).
2. <b>Ommitted List table</b>, which have the same columns with the "Promoted List table", Except for the "Expected New Level" Colunm, which replaces the "New Level" Column.
I am done with the job of the tables on VB.net (2010), but I need help in the area of the next form, which will first check the <b>Eligibility</b> of a staff for promotion, before assigning the staff for the next promotion. This has to deal with the "<b>Present year</b>" being worked upon and "<b>The Last Year of Promotion</b>" of the Said Staff.
First it has to check if the "<b>Present Year</b>" - "<b>Last Year of Promotion</b>" is greater than or equal to three (3). <b>If</b> it is greater than or equals three (3, Staff is eligible for Promotion and focus is set to the Promoted List table for the staff to be entered into the database, for the next promotion; <b>Else</b>, the staff is not eligible and an error message is displayed.
I'm using the VB.net 2010 for this project
|
|
|
|
|
Umm what is your expectation here, you have described a few tables and told us what you want to do but have not defined or asked a question.
We can't and won't build your app for you, you need to do that and when you have a specific problem with your code then we may be able to help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I was able to achieve that without the date time function.I made it as simple as i could do. But I am having a problem on deployment (publishing)of the program, to beable to run on any system, other than the one I developed with. when I tried to publish,it did publish, but if I try to access the forms that contains a link to the database, I keep getting error, "Microsoft ...... 12.0 or 4.0 (when I saved in 2002 - 2003 format), not installed on local machine. I want to send you the folder that contains the project, but can't, as I cant see where to attach files here.
|
|
|
|
|
What is the better way of indexing the foreign key?
Create Table table3(
t3_id int not null auto_increment,
t1_id int not null,
t2_id int not null,
primary key (t3_id),
index IX_index (t1_id, t2_id), // this is my concern
constraint FK_t1 foreign key (t1_id)
reference table1(t1_id),
constraint FK_t2 foreign key (t2_id)
reference table2(t2_id));
or
Create Table table3(
t3_id int not null auto_increment,
t1_id int not null,
t2_id int not null,
primary key (t3_id),
index IX_t1 (t1_id), //this is my concern
index IX_t2 (t2_id), //and this
constraint FK_t1 foreign key (t1_id)
reference table1(t1_id),
constraint FK_t2 foreign key (t2_id)
reference table2(t2_id));
This is for innodb tables, I dont have a broad understanding how the btree works. But as far as I know, the first table will save the indexes on single area while the second is not (correct me if I am wrong). And if it is what is the pros and cons if I will put it on a single index or multiple index?
Thanks.
modified 10-Feb-17 6:41am.
|
|
|
|
|
They are different ways and which is 'best' depends on your needs.
The first creates a composite index, and puts two fields in that list. The second one creates two separate indexes for those fields.
Are you going to be locating a lot of records by searching for t1_id, and when found, looking in that set for t2_id? Or are you going to be locating a lot of records by searching for either field?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
For now its just a concept table, for me to understand when to use the first or the second one.
can you explain to me when should I use the composite index or the other one? please. Thanks
base on your reply your saying that, if I use the t1_id and t2_id to satisfy a where clause frequently, I should use the composite key other wise I should use the separate index for every field. Am I right?
How about joining the three tables?
Thanks.
|
|
|
|
|
Think of it like a phone book: the data has a composite key of (Surname, Forename).
If you're always going to know the surname of the records you're looking for, then the index works.
But if you ever want to search for a particular forename, without knowing the surname, then the index is no help. You have to resort to reading through the entire book to find the matching records.
If you wanted to do that regularly, you'd create a separate index sorted by forename. That would make it easier to find records with a particular forename. But it would also mean you'd have more work to do when you insert, update, or delete records.
Which option you choose will depend on your data, how you're going to be querying it, and how often you're going to be changing it. For example, if you're only going to be using t1 as a lookup, and never searching for records with a particular t1_id, then there's probably not much point having an index on it.
It basically comes down to a combination of instinct and performance measurement.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks appreciated .
|
|
|
|
|
This Foxpro program keeps using spaces on the invoice number column in front or the data. Perhaps that was the norm back then before I started programming
E.G.
"70155" would be " 70155"
So I not sure if the column is fixed, or if every invoice number starts with a " " blank space.
e.g. " 155"
I wrote this placing a space in front of the invoice number, but afraid it may backfire on me when the invoice number grows larger
Dim c5 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01H.dbf SET FPRICE = 0.00 WHERE FINVNO = ' " & pFINVNO & "' AND FITEMNO = '" & pFITEMNO & "'", connection)
I thought using a wildcard, but I've been searching for a couple of hours and cant get things like $,% to work
If it ain't broke don't fix it
|
|
|
|
|
This works at the moment, I'll run with it today
Dim c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = " & pFPRICE & ", FAMOUNT = " & pFPRICE & " WHERE FINVNO LIKE '%" & pFINVNO & "%' AND FITEMNO = '" & pFITEMNO & "'", connection)
If it ain't broke don't fix it
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
