SQL Server for C# Programmers
I'm a pretty good C# programmer who needs to learn SQL Server. What's the best way for me to learn SQL Server/Database development? Note: I'm a total newb when it comes to DB's and SQL. ...
sql
Subtyping database tables
I hear a lot about subtyping tables when designing a database, and I'm fully aware of the theory behind them. However, I have never actually seen table subtyping in action. How can you create subtypes of tables? I am using MS Access, and I'm looking for a way of doing it in SQL as well as through the GUI (Access 2003). Cheers! ...
Update subset of data in sqlserver
Hi, I have been given the following request. Please give 7% of the current contacts registered to each of the sales associates to the new sales associate ('Peter'). What I decided to do was to get the total records for each sales associate and calculate 7% of the records. For example David has 200 200/7% = 14 SELECT TOP 14 Contact...
Linq2sql Find Top Subscribers Question
Hello all, It has been a while since I have wrote any code due to military obligations (Afghanistan, Reserves) and I have a question with regard to linq 2 sql (hell, I would do this as a stored proc at this point...I really am rusty). I have a table of feed names, and those feeds have subscribers in another table (foreign key associatio...
Writing data back to SQL from Excel sheet
I know it is possible to get data from a SQL database into an excel sheet, but i'm looking for a way to make it possible to edit the data in excel, and after editing, writing it back to the SQL database. It appears this is not a function in excel, and google didn't come up with much usefull. ...
Time Slot SQL Query
I'm trying to write a query to see if an engineer visited his job in a agreed time slot. This is my query so far: SELECT v.[VISITDATE], CONVERT(VARCHAR, v.[STARTTIME], 105) AS 'Startdate', CONVERT(VARCHAR, v.[STARTTIME], 108) AS 'StartTime', CONVERT(VARCHAR, v.[bookeddate], 105) AS 'BookedDate', CONVERT(VARCHAR, t.[star...
Slow bulk insert for table with many indexes
I try to insert millions of records into a table that has more than 20 indexes. In the last run it took more than 4 hours per 100.000 rows, and the query was cancelled after 3½ days... Do you have any suggestions about how to speed this up. (I suspect the many indexes to be the cause. If you also think so, how can I automatically drop...
Compare Images in SQL
Hi, What is the best way to compare to Images in the database? I tried to compare them (@Image is the type Image): Select * from Photos where [Photo] = @Image But receives the error "The data types image and image are incompatible in the equal to operator". ...
Set Based Operations and calling Stored Procedures
I am working on a stored procedure that performs some operations on students in the class In the last step it updates status of some of the students based on some criteria. It is all pretty straight forward but I have a dilemma here. Basically there is an existing sp in the system called pUpdateStudentStatus(studentID, statusID, com...
How to get top "N'th Maximum" value alone from a table in MS SQL?
Assume that there is a 'Employee' table with salary as one of the columns. I want to get the 'Top N'th maximum salary alone from the table. How this can be fetched easily? ...
max count together in an sql query
I have the following table (simplified) in an oracle db: productId | modelDescription 1 | thing 2 | another thing 3 | not a thing 4 | thing I want to select the modeldescription which has the highest appearence in this table. The problem is that there can be near...
What does the term "Tuple" Mean in Relational Databases?
Please explain what is meant by tuples in sql?Thanks.. ...
Best way to get the next id number without "identity"
I have to insert some records in a table in a legacy database and, since it's used by other ancient systems, changing the table is not a solution. The problem is that the target table has a int primary key but no identity specification. So I have to find the next available ID and use that: select @id=ISNULL(max(recid)+1,1) from subscri...
SQL CLR Stored Procedure and Web Service
I am current working on a task in which I am needing to call a method in a web service from a CLR stored procedure. A bit of background: Basically, I have a task that requires ALOT of crunching. If done strictly in SQL, it takes somewhere around 30-45 mins to process. If I pull the same process into code, I can get it complete in sec...
Question about inserting data into a table part of a 1:m relationship
When inserting records to a database table which has a 1:m relationship to another table, is it not best practise to ensure that the related table is updated (inserted) with a new record? Is there any consequence if just one of the related tables is updated? Also, if I update the table on the m side of the relationship (for example, a r...
SQL Server Dynamic Order By Problem
I'm trying to use a dynamic order by in a stored procedure so I can pass the order I want the data returned into the stored procedure as a parameter. This works fine for VARCHAR fields however if I try to sort an int or datetime field it errors the code I have is as follows DECLARE @ORDERBY INT SET @ORDERBY = 1 SELECT TOP 10 * FROM TBL...
MS SQL Server - Invalid characters in parameter names.
I need to know what are invalid characters to use in a SQL parameter name. Given something simple like SELECT * FROM tblTest WHERE testid = @[X], if X contains a hyphen, for instance, the statement will fail. Can anyone give me a definitive list or point me in the direction of one. Thanks in advance. ...
Create an index on SQL view with UNION operators? Will it really improve performance?
I am trying to create an index on the following view: SELECT 'Candidate' AS Source, CandidateID AS SourceId, LastName + ', ' + FirstName AS SourceName FROM dbo.Candidates UNION SELECT 'Resource' AS Source, ResourceID AS SourceId, LastName + ', ' + FirstName AS SourceName FROM dbo.Resources UNION SELECT 'Deal'...
Hibernate: update to sum from other table
I'm trying to replicate this query from MySQL to HQL: *UPDATE users u, mines m SET u.mana = u.mana + COALESCE((SELECT SUM(m.mana_rate) FROM mines m WHERE m.user_id = u.id), 0)* Simply doing this in HQL: update User u set u.mana = u.mana + (select coalesce(sum(m.manaRate), 0) from Mine m where m.userId = u.id) Gives following error: Exc...
Constraint for one-to-many relationship
We have a two tables with a one-to-many relationship. We would like to enforce a constraint that at least one child record exist for a given parent record. Is this possible? If not, would you change the schema a bit more complex to support such a constraint? If so how would you do it? Edit: I'm using SQL Server 2005 ...