|
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.
|
|
|
|
|
All true, and if one wanted to fully emulate an enum one would also need an extra column for the integer value.
But you can't add new members to an enumeration without recompiling either.
Anyway, I got the impression that the OP only wanted to limit the items in a column, and if your objections is of no concern for the OPs intended usage, this is a simple way to achieve a limited list of items.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
Jörgen Andersson wrote: the OP only wanted to limit the items in a column
Which is likely true, but I hope to open his eyes to a broader solution that he may not have considered yet.
Reducing record size may not be as important with today's databases and storage availability, but it's still better to store a foreign key rather than a string, particularly if you may need to select records with given values.
Having a table with the keys and values also allows for easier loading of a list from which the user can select items.
|
|
|
|
|
Quite right.
I'm usually pushing normalization myself.
"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull
|
|
|
|
|
|
One looks like you may have answered an already solutioned question another I could see no reason why and another someone explained why the down vote.
Down votes come with the territory, some will simply vote b/c you did not supply the codz (although you seem to be happy to do so!).
You may also have picked up a univoter, they are also part of the territory, relax, they get bored really easily and move on if you leave them. Bitching validates them and they feed off that.
If you have a real problem with it you can try the bugs and sugs[^] forum
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Looks like you got this one downvoted for posting in the wrong forum!
Use the best guess
|
|
|
|
|
hi to all
ho can i get top 1 from my table.
i have a event log table that 3 event inserted in it i want to get top 1 each event by desc in it .
my code is but this code is wrong and correct code probably similar to this code
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid )
ORDER BY Time DESC
) )
thanks for any help
|
|
|
|
|
How about something like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs AS elog
INNER JOIN (
SELECT [Guid]
FROM EventLogs elog1
INNER JOIN (
SELECT [Event], MAX([Time]) AS MTime
FROM EventLogs
GROUP BY [Event] ) AS t ON elog1.[Event] = t.[Event] AND elog1.[Time] = t.MTime
) AS elog3 ON elog.[Guid] = elog3.[Guid]
|
|
|
|
|
thanks for your answer
this problem has been fix with some few change like this:
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress
FROM EventLogs
WHERE ( [Guid] = ( SELECT TOP ( 1 )
[Guid]
FROM EventLogs AS elog
WHERE ( ReferenceGuid = EventLogs.ReferenceGuid AND Event=EventLogs.Event)
ORDER BY Time DESC
) )
|
|
|
|
|
Or you can use row_number, can't remember the syntax but there are good examples in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
With SQL Server 2005, we can use Row_Number() function and Partition By clause to number rows in categories
So you can partition your data in Event category and filter only the rows with row number is 1
So you will get only 1 row per category
with cte as (
SELECT [Guid] ,
UserGuid ,
ReferenceGuid ,
ReferenceID ,
[Event] ,
Comments ,
[Time] ,
IPAddress,
rn = ROW_NUMBER() OVER (Partition By Event Order By Time Desc)
FROM EventLogs
)
select * from cte where rn = 1
Please check the following URL for similar sample code http://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx[^]
|
|
|
|
|
Long story short, what do you folks use to update a database in unison with a given software release? We have a 10+ year old in house tool that's ending it's life (SQL Server is ending SQL-DMO support). This is a company-written tool to move a DB from Version N to N.N in unison with a software release.
Do you guys write your own, or what links can you send me? I've been playing with RedGate's SQL Source Control, but our DB is big enough that it chokes on it. Small DBs seem to fly, but ours is a big, multi-year multi-product suite type of db.
Apex? What do you use or do you build your own? Ten years is a nice life cycle for this product so if we write one that's good for another ten, okay. I'd rather buy one first.
thanks for the input.
Think of how stupid the average person is, and realize half of them are stupider than that. - George Carlin
|
|
|
|
|
Backup/restore on a new server, I'm pretty sure you can restore an older version into a current database. Not sure if the version range is too wide though. Unless you have a pressing need to update the code in your database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
Using SQL Server 2008 R2. I have a stock table, with the following fields: warehouse, product, long_description, physical_qty.
All products exist in warehouse '01'. Some of the products exist in warehouse '03' Faulty (or NG) goods get transferred to warehouse 03.
I need a query to show distinct(product), long_description, physical_qty (for warehouse 01), physical_qty (for warehouse 03)
Example data:
warehouse product long_description physical_qty
01 00-00001 Item 1 100
03 00-00001 Item 1 5
01 00-00002 Item 2 200
Desired Result:
Product Description Stock_Qty NG_Qty
00-00001 Item 1 100 5
00-00002 Item 2 200
I have tried this, but don't get distinct products (i.e. Item 1 appears in two rows)
Select distinct(product), long_description, case warehouse when '01' then physical_qty else 0 end as [Stock_Qty], case warehouse when '03' then physical_qty else 0 end as [NG_Qty] from vektron2.scheme.stockm group by product, warehouse, long_description, physical_qty order by product
|
|
|
|
|
|
Thanks Mycroft - will give it a try tonight
|
|
|
|
|
I would like to search all User defined tables in a database for given condition.
Say for example my condition is (playerid = 10000052 or tripid = 10000124 ) and TripDtm =
'2013-02-03 00:00:00.00 +00:00'
I would like to get result from the tables which return at least one record for the condition.
How to achieve this? Please do assist.
|
|
|
|
|
Have a look at this article - it seems to do what you are looking for
Search for a Value Throughout Your Database[^]
It's well known that if all the cat videos and porn disappeared from the internet there would be only one site left and it would be called whereareallthecatvideosandporn.com
|
|
|
|
|
Hi i created a web service in PHP using mysql now i wan to change the same into SQL is there any option.
---------------------------------------------------------------
-- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 21, 2013 at 11:46 AM
-- Server version: 5.5.16
-- PHP Version: 5.2.9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `lt`
--
-- --------------------------------------------------------
--
-- Table structure for table `user_details`
--
CREATE TABLE IF NOT EXISTS `user_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`First_Name` varchar(255) NOT NULL,
`Last_Name` varchar(255) NOT NULL,
`Middle_Name` varchar(255) NOT NULL,
`Door` varchar(255) NOT NULL,
`Street` varchar(255) NOT NULL,
`Building` varchar(255) NOT NULL,
`Area` varchar(255) NOT NULL,
`Theil` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `user_details`
--
|
|
|
|
|
MySQL is basically SQL. Just try it out and correct the errors you stumble upon.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
