|
Since a view can become out of sync with the underlying table, it is not a good idea to insert through a view, [sarcasm]unless you are using MS Access[/sarcasm].
Another problem is, if there are multiple tables in the view things go wrong quickly.
Now if he would have said you should use a stored procedure to do the insert then I would say he has a point. However it is not wrong to insert directly into a table.
|
|
|
|
|
Eric Whitmore wrote: if I do "inserts" into "tables" I am doing it "wrong
You should ask why. There could be an architectural reason such as an intent to move to another schema or legacy reasons.
Could just be an odd way to provide a database layer though. For that though I use stored procs.
|
|
|
|
|
jschell wrote: I use stored procs
Right, I don't (willingly), but I know many do and, of the two techniques, procedures make more sense.
|
|
|
|
|
I ran across this attitude in the 90s (and not since) I believe it was based on security, there was a difference between table and view security back then. I would be very interested in your DBAs reasoning behind the statement.
PieBald
You can insert into views if there is 1 table in the view (how dumb is that) or the relationships are not too complex. I have never explored this as I also think it is a horror waiting to happen.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: in the 90s
At that time I was using Oracle (6) and I remember asking the DBAs why the views (there weren't many) had an _v suffix and the response was "to remind the developers that they are views and therefore readonly".
|
|
|
|
|
I think I was working with MSSQL 6.5!
Try creating a view of only 1 table, no joins, and inserting/editing a record. Now add an inner join and repeat.
Even though I have SSMS open I'll not bother as it is a complete WOTAM.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can insert into views if there is 1 table in the view (how dumb is that)
It's quite useful if you want to filter the contents of a table for a certain group of users.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: It's quite useful if you want to filter the contents of a table for a certain group of users.
Why would you do that in the database versus the application layer?
And of course in general when you say 'user' if you mean an actual database user then there can be negative impacts such as licensing.
|
|
|
|
|
Because some companies are quite divided.
The right place to do it is in the application, but I've been working for companies where the database department were responsible for that one department couldn't see the same columns as another department.
It's a lot easier to keep track on this with a view than with column permissions.
When it comes to licensing it doesn't matter. It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: Because some companies are quite divided.
Certainly. And some companies need to create their own database.
But in the general case neither of those are true.
Jörgen Andersson wrote: It's the number of actual users that counts, not the number of users you setup in the database. It's a common misconception.
I know how it works.
The fact that user licensing exists at all specifically indicates that support in the database per user is something that is in fact significant.
Unless you are claiming that only a single user connects at a time, then my comment about user licensing stands. And if you are claiming that then it is far from what any normal business would use.
|
|
|
|
|
Jörgen Andersson wrote: f you want to filter the contents of a table for a certain group of users
Sure but that is one of the uses of a view - a very old fashioned use, but still valid. Who puts authorisation logic at that level these days. Apps no longer have to rely on the database for authorisation, very 80s 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I know, see my answer to jschell.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Yeah I read that and thought poor bastard, I just hope you are not working with one of those old EIS systems as well!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm at a different place now, with a different set of pains.
Mycroft Holmes wrote: I just hope you are not working with one of those old EIS systems as well!
Depends on what EIS stands for. If it is Executive Information System, then yes kind of.
It's my own design though. It's the users that's the main pain now.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Hi Srs.,
I am searching in a lot of sites a way to justify my textboxes on the sql server reports, but I don't found yet.
In a site they suggest I to use html/css fields, but the SSRS don't recognize the tag "justify".
Someone here have some kind of workaround for my issue?
Thanks for your attention!
|
|
|
|
|
|
The db I am testing is locked ATM doing some data-loads so I can't get various potential methods of doing this so I figured I would ask. I have a database with each table having its own file group and each file group has one file. Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
My thought would be to just copy the physical files and then through some magical T-SQL have the files appear as the new table. I believe in My-SQL copying db files is all that is required but SQL Server is a special lady. Any thoughts? I am only dealing with about 60 million records an hour so not that much data.
|
|
|
|
|
Ennis Ray Lynch, Jr. wrote: Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
Yes. Detach, xcopy, attach.
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ] (From MSDN[^])
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for your reply, unfortunately, your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data.
What I am looking for is some black magic to take files from a separate database and bring then into a different database without bringing it offline. Assuming both databases have the exact same configuration, this is technically possible (otherwise you couldn't add new files and file groups while SQL Server is running) but it is likely a dark-art. Bringing the database offline is not a valid solution for me needs.
|
|
|
|
|
Ennis Ray Lynch, Jr. wrote: your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data.
Not necessarily so; you can attach it under a different name to the same server. Next, you SELECT INTO the tables from Database1.Dbo.TableName to Database2.Dbo.TableName 
Ennis Ray Lynch, Jr. wrote: What I am looking for is some black magic
Aight, sacrifice a chicken and a donkey at the next full moon. More seriously; if you have access to a DBA, ask him/her how they'd implement horizontal partitioning. You could do something similar on a more simpeler level; you'd have to change your select-query to dynamically check for new databases, and to union it along. Could be done by querying sys.databases and building the select/union from that.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I'm using VS2010 to create Data Base in SQL server.
I want to create column that will be of type enum.
For example the enum:
public enum Equipment
{
Tank,
Suit,
Fins,
Mask,
Snorkel
}
And the column will be called DiveKit, the values in that column can be one of the Equipment enumeration members.
Thanks
|
|
|
|
|
Sql Server doesn't have an Enum datatype. You can store it's value-representation (int) or it's string-representation (varchar). It wouldn't do range-checking by default.
Simple example below on having range-checking by creating a table for the enum and referencing it;
CREATE TABLE enumEquipment([Label] VARCHAR(10) PRIMARY KEY)
INSERT INTO enumEquipment (Label) VALUES ('Tank'), ('Suit'), ('Fins'), ('Mask'), ('Snorkel');
CREATE TABLE someTableUsingTheEnum
(
Id INT IDENTITY(1,1) PRIMARY KEY,
EnumValue VARCHAR(10) REFERENCES enumEquipment (Label)
)
INSERT INTO someTableUsingTheEnum (EnumValue) VALUES ('Tank')
INSERT INTO someTableUsingTheEnum (EnumValue) VALUES ('Micky')
SELECT * FROM someTableUsingTheEnum
DROP TABLE someTableUsingTheEnum
DROP TABLE enumEquipmentIt'd be better, performance-wise, to use the integer-value of the enum. Just cast it to an int, and store it in an int in Sql Server.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Perhaps my GenOmatic[^] article will provide some insight.
|
|
|
|
|
You can use a check constraint on the table if you just want the column limited to a list of values.
ALTER TABLE EQIPMENT
ADD CONSTRAINT EQUIPMENTTYPE_CHK CHECK (DIVEKIT IN ('Tank', 'Suit', 'Fins', 'Mask', 'Snorkel')
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
I'd be concerned about the need to easily add new members. And agnosticism.
Plus, having a table allows the addition of more information related to the items.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
