The query below fetches the wageTypeID based on the current active hiring info record and salary record. SELECT wt.wageTypeID FROM TimeSheet t INNER JOIN Employee e ON e.employeeID = t.employeeID INNER JOIN Salary s ON s.salaryID = t.salaryID INNER JOIN Wage w ON w.wageID = s.wageID INNER JOIN EmpHiringInfo ehf ON ehf.Emp...
my question specifically about sql-server, but probably can be answered by anyone with any database background if i want table A to have a 1:1 relationship with table B on a certain column, should i somehow modify the CREATE TABLE statement to identify this relationship or is this something that is not done at all (and rather it is hand...
Hi There! I am making a CMS of sorts and, of course, it will have a blog. So, this might be a pretty noob question, but, from a database optimization point of view, would you use a varchar(max) or a varbinary(max) to store the body of a blog post? ...
I am trying to figure out which data types I should use for my tables. Does someone have a very good, simple, tutorial on SQL Server 2008 datatypes (has to be practical!) ? ...
If I am within a nested stored procedure, I would like to be able to get the procedure ID of the procedure that is one level up. Example: create proc procedure1 as print 'Current proc id: ' + @@PROCID print 'Parent proc id: ' + @@PROCID_PARENT --or something? go create proc procedure2 as exec procedure1 go exec procedure2...
i am trying to add a constraint to a datatype of char(1) i would like the user to only be able to enter Y or N or n or Y i clicked on check constraint in the CHECK CONSTRAINT EXPRESSION window what am i supposed to enter? ...
If I have a table with huge amount of data...and If I do incremental delete instead "one time delte"..what's the benefit ? Onetime delete DELETE table_1 WHERE BID = @BID AND CN = @CN AND PD = @PD; Incremental Delete While (1=1) Begin DELETE TOP (100000) FROM table_1 WHERE BID = @BID AND CN = @CN ...
I am doing: BULK INSERT CSVTest FROM 'c:\csvtest.txt' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n') GO --Check the content of the table. SELECT * FROM CSVTest GO --Drop the table to clean up database. SELECT * FROM CSVTest GO Is it possible to insert only specific fields FROM the csv? ...
This requires a bit of background. I'm creating a web app (ASP.NET/VB.NET with SQL Server backend) that will accept an application to receive funding, and this form has contact info fields for three people. Because the information is the same for all three people (name, email, phone), I chose to store contact info in a table separate f...
Hi, We are building the server side of some gaming platform where a great load of mobile devices (a.k.a - mobile phones for time being) connect to a server, send some small information each second (or even less) and receive some small response based on calculations. The data has a DB back end (sql server for now). We have implemented ...
USE AdventureWorks2008R2; GO INSERT INTO myTestSkipField SELECT * FROM OPENROWSET(BULK 'C:\myTestSkipField-c.dat', FORMATFILE='C:\myTestSkipField.fmt' ) AS t1; GO i dont want to specify a formatfile!!! i just want to specify the format inline. is this possible? ...
i am inserting values like '$5.99' (or trying to insert) into a money field. it doesnt like the dollars sign i am actually doing a bulk insert from a csv file. one of the columns in the csv file has money in it with a dollar sign can you please help me figure out how to do this bulk insert with the $ sign into a money field? Msg 4864,...
I am working on a program in which you can register complaints. There are three types of complaints: internal (errors from employees), external (errors from another company) and supplier (errors made by a supplier). They hold different data which cannot be shared. I currently have 4 tables (complaint, employee, company and supplier). Her...
i have a very simple excel file that i need to import into a table in sql server 2008. one of the fields is a bit complex and i dont think it can be saved effectively to a csv, since it sometimes has comas and single quotes in it. it screwed up the formatting when i save to a csv. so i would like to try to import directly from the xls f...
I have read that it is a good idea to have one file per CPU/CPU Core so that SQL can more efficiently stream data to and from the disks. Ok, I can see the benefit if they are on different spindles, but what if I only have one spindle (4 drives in Raid 10) for my data files (.mdf and .ndf), will I still benefit from splitting the data fi...
Hi, Is there a way in SQL Server that can show the Fiscal Year (begins on October 1 and ends on September 30) from a table which has a date column (1998 to 2010). Here is what I have done: select 'FY1999' as FY, site, count(*) from mytable where mydate >='10/1/1998' and mydate <'10/1/1999' group by site select 'FY2000' as FY, site, co...
As well as CHAR (CHARACTER) and VARCHAR (CHARACTER VARYING), SQL offers an NCHAR (NATIONAL CHARACTER) and NVARCHAR (NATIONAL CHARACTER VARYING) type. In some databases, this is the better datatype to use for character (non-binary) strings: In SQL Server, NCHAR is stored as UTF-16LE and is the only way to reliably store non-ASCII charac...
I have a query that fetches employee's name, date, working hours, wage and calculate salary based on working hours * wage. The minutes in working hours that is being calculated are discarded. I only get the value in full hour. Snapshot example: My main concern is on workingHours and wageAmount workingHours is displayed as 5....
In order to debug a SQL Server 2008 stored procedure, I would like to change the account that the SQL Server (MSSQLSrver) service is running under, namely, changing the account from "Network Service" to my current user account. However, if something goes wrong, I would like to be able to change it back and I am concerned that since this...
Given two scenarios on SQL Server 2008/2005 - 1 Table has 5 rows 2 Table has 1 million rows If we need to update a few rows, what is is efficient and why? 1) UPDATE the required columns 2) DELETE the row and INSERT new row with the updated information ...