|
|
Nope nope, not this time, nuh uh. But there are better ways of generating integer Ids.
|
|
|
|
|
|
Only if you don't use functions or nested procedures or triggers. Scope_Identity() is a more robust solution. Learn to use that instead (reteach the fingers).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, but better to get the ID one way or another before inserting the record -- as we did with sequences in Oracle. Identity columns are the devil's work.
At work recently I tried to copy some records from one table to another, but couldn't because the IDs wouldn't be the same. 
A thousand deaths upon the idiot who created identity/auto-increment columns. 
|
|
|
|
|
PIEBALDconsult wrote: At work recently I tried to copy some records from one table to another, but
couldn't because the IDs wouldn't be the same
I worked with a company that insisted their product worked on Oracle.
The schema they delivered obviously originated from MS SQL Server and used quoted identifiers for every single identifier. (My guess - they used a tool to 'convert' the schema prior to delivery to us.)
Yet despite that I neither condemn Oracle nor MS SQL Server. I do however blame the company for making claims that obviously they had not designed for.
I can only wonder, was the table that you had difficulty with designed to be copied from one location to another while maintaining foreign keys (which I can only presume was the actual problem with ids.)
If it wasn't are you suggesting that one should create designs that meet all possible needs of the business of the future? Myself I know that I have yet to meet any developer that is even close to being able to tell the future and the alternative, designing for every possible need, is neither possible nor cost effective.
So which of those alternatives are you suggesting business developers should rely on?
|
|
|
|
|
jschell wrote: was the table that you had difficulty with designed to be copied from one
location to another while maintaining foreign keys
Well, I was using the tool built by the infrastructure team for copying data between dev/test/prod, it's the only way to do it... so, yeah, obviously not.
jschell wrote: any developer that is even close to being able to tell the future
But you should know the past and learn from it.
jschell wrote: So which of those alternatives are you suggesting business developers should
rely on?
Don't use identity/auto-increment columns, because you never know what the future holds.
|
|
|
|
|
PIEBALDconsult wrote: Well, I was using the tool built by the infrastructure team for copying data
between dev/test/prod, it's the only way to do it... so, yeah, obviously
not.
So you ran into a poor design. That of course has nothing to do with a specific technological implementation.
PIEBALDconsult wrote: But you should know the past and learn from it
Correct - when one creates a design that doesn't actually meet the known needs of the business then one should learn to create designs that do meet the needs.
Don't see that that has anything to do with this discussion.
PIEBALDconsult wrote: Don't use identity/auto-increment columns, because you never know what the
future holds.
No that isn't the conclusion. The conclusion is that you were dealing with a design that didn't meet the known business needs. Nothing more.
There are an infinite number of ways to fail in that regard using absolutely any technology in absolutely any way.
|
|
|
|
|
Ummmm... so how do you copy records between tables that have identity columns? 
|
|
|
|
|
PIEBALDconsult wrote: Ummmm... so how do you copy records between tables that have identity
columns?
If I have a business need to copy records and one which is not just replication and I didn't have any needs that precluded GUIDs then I would use GUIDs.
That however is far different than saying that they should be used for every possible scenario.
|
|
|
|
|
jschell wrote: I would use GUIDs.
OK, me too.
jschell wrote: used for every possible scenario
I'm pretty sure I didn't say they should.
|
|
|
|
|
PIEBALDconsult wrote: I'm pretty sure I didn't say they should.
You said in another subthread "True, there is never a need" in regards to PK as a sequential integer.
I took that to mean that one should use GUID - always (implicit within "never").
|
|
|
|
|
jschell wrote: I took that to mean that one should use GUID - always (implicit within "never").
No, that's not what I meant, although many seem to take it that way.
|
|
|
|
|
I am trying to create a report reading from various databases. I can see that it is possible to write code for table cells in the report. But I don't like the small window that is provided to you for writing code. I would prefer writing code in a .NET library (a separate project) and then reference the code from the report.
Is it possible?
If yes, how do I reference code in my own custom library from my report?
|
|
|
|
|
Found the solution myself:
1. Place custom assembly in folder: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
2. Right click report (in design view), select "Report Properties", then add reference to the custom assembly.
Thanks to myself. 
|
|
|
|
|
Xiangyang Liu 刘向阳 wrote: Thanks to myself
+5 for talking to yourself and for posting the solution - might help someone who is working on the same
I are Troll 
|
|
|
|
|
Hi,
I have problem where I have get list of all tables on which full text index can be applied with their key names.
Please help, its urgent.
Thanks.
|
|
|
|
|
awadhendra tiwari wrote: I have problem where I have get list of all tables on which full text index can be applied with their key names.
SELECT TC.[TABLE_SCHEMA]
, TC.[TABLE_NAME]
, CCU.[COLUMN_NAME]
, TC.[CONSTRAINT_NAME]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.[CONSTRAINT_NAME] = CCU.[CONSTRAINT_NAME]
WHERE TC.[CONSTRAINT_TYPE] = 'PRIMARY KEY'
AND EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.[TABLE_NAME] = TC.[TABLE_NAME]
AND C.[DATA_TYPE] IN (
'char',
'varchar',
'nchar',
'nvarchar',
'text',
'ntext',
'image',
'xml',
'varbinary',
'varbinary(max)'
)
)
ORDER BY TC.[TABLE_NAME]
awadhendra tiwari wrote: its urgent
The people that visit this site do so in their spare time. Rushing would be inappropriate.
I are Troll
|
|
|
|
|
Hello All,
Whenever I run any simple select query, it shows me following message in Execution Plan. I have created relevant indexes on the table.
Query Cost (relative to the batch): 100%
Can anyone tell how can I reduce this Query Cost ?
Thanks
|
|
|
|
|
You are misunderstanding what Query Cost is. If you run any single query, the cost will always be 100%. If you run multiple queries, the total cost of all of the queries in the batch added together will equal 100%. The value is used to compare the relative cost of each query in the batch as the message says.
|
|
|
|
|
Hello,
I have the following situation which I can seem to resolve. When I do an "ALTER TABLE" in mysql (v5.5) it hangs in with "waiting for metadata lock". Here is how I got there:
- i have an xml which i want to import into a table; i use the elements in the xml to create columns in the table like this:
XML example
<name> some name </name> <address>dsada</address>
there are some nodes in the xml that have different number of elements, like:
<name> some name </name> <address>ds adsa</address> <phone> fndjsf</phone>
SQL queries
create table `person` ( `id` bigint(12) unsigned NOT NULL auto_increment ,`name` varchar(200) NOT NULL ,`address` varchar(250) NOT NULL ,PRIMARY KEY (`id`),) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into person(name, address) values ...
the column names are read from the xml, so are the values. If the for loop reads a column name that hasn't been created in the table yet, like "phone" it returns a sql exception "column name "phone" not found" and tries an alter table before inserting the values
alter table 'person' ADD COLUMN `phone` varchar(20) NULL default NULL;
here is where the program hangs. i've checked in mysql admin for the session and the state is "waiting for metadata lock"
how can i pass this? or how do i remove the table metadata lock created by the previous insert which raised the exception. (the import is made in java that's why i use a for loop to parse the xml and get the element names and values)
Thanks
|
|
|
|
|
You may need to commit your changes so that the alter table command will not wait.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
i am commiting the changes. i have also tried to unlock tables before alter but to no success. think i have to re-think the whole process.
|
|
|
|
|
Hi !
I have a very odd problem.
I am using LinqtoSql in my project in c#. , and what i do is i create a new table and set its column through sql server management studio express (2008 r2) , and I add a class file to the project(in visual studio) and using linq i do mapping of that table i made in Sql studio.
e.g
[column]
public string firstname;
[column]
public string lname;
etc etc.
My question is , if i want to make a new table and add its column etc through the windows forms application in c# at runtime (using sql commands etc) , how can i use linq to automatically map the newly created table without manually editing the code?
What should i do, i have to use LINQ to SQL.. PLZ HELP
|
|
|
|
|
OK, so you create your DB table at run-time. But what about the class to which you are mapping the newly created table, is it created at run-time as well? If it is (i.e. you generate and compile the code, or emit CLR byte code) you could provide [column] attributes too: there is no "LINQ-specific magic" in .NET, so you can do nearly anything the compiler can.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
