What the size that you use for common database fields, like firstName, lastName, Email, password, etc? I see these common fields in a lot of databases of blogs, forums, e-commerces, etc. But I don't know if there is some reference or default for the size for that common fields. So, I want to know what the method/reference/basis that you ...
Is there a way to insert a dynamic number of rows from within sql server (.sql script) given the value of a look up, and setting one column for each insert? I want to attach a row with the foreign key of every row in a different table. For instance: table 1: 1 j k l m n 2-(fk) 2 j k l m n 3-(fk) 3 k u y k l 2-(fk) table 2: 2 hi you ...
Hi, I have a table, that has a simple structure: user_id, month, balance, balance_type I want to display selected balance types per month for each user that is: user_id, month, balance_1, balance_2, balance_3, balance_... So from data: ROW user_id month balance balance_type 1 5667 09 20 2068 2 5667 0...
inspired by this question since i do not find any good sql casts out there and i fail to find any good ones to this day online. ...
Hi, I have a stored procedure returning ID, Name, Descriptions and takes no input parameters. However, I am interested in how many results do I get. I expected something like this work: SELECT COUNT(*) FROM EXEC MyStoredProcedure But I get the following error in SqlServer Managment Studio: Incorrect syntax near the keyword 'EXEC'. ...
I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me: 1. Run both and time each query 2. Run both and get "Query Cost" from the actual execution plan Here is the code I run to time the queries... DBCC FREEPROCCACHE GO DBCC DROPCLEANBUFFERS GO DECLARE @start DATETI...
Hello everyone, I have variable length character data and want to store in SQL Server (2005) database. I want to learn some best practices about how to choose TEXT SQL type or choose VARCHAR SQL type, pros and cons in performance/footprint/function. thanks in advance, George ...
Hi all, I'm converting some data in SQL Server 2005. I have a table update like this: update Invoices set Invoices.InvoiceReference = 'NewRef' where Invoices.InvoiceReference='Unknown' But what I'd like to plug in instead of 'NewRef' is the output from a stored procedure that uses parameters from the columns of the Invoices table. T...
Hi Im trying to use some T-SQL to move some files from one directory to another. Im using xp_cmdshell to call the move command Just like this: create table #output(line varchar(2000) null) insert into #output exec master..xp_cmdshell 'move /y "D:\files\*.txt" "D:\oldfiles"' But the files inst move and the #output table contains this o...
What is the difference between the following query: SELECT * FROM employee WHERE NOT(start_date > '01-JAN-1970'); and this query: SELECT * FROM employee WHERE start_date < '01-JAN-1970'; Is there any difference, and if so, how is NOT(x > y) used differently from (x < y). Can anyone provide an example? Thanks. ...
I have two SQL queries, where the first one is: select Activity, SUM(Amount) as "Total Amount 2009" from Activities, Incomes where Activities.UnitName = ? AND Incomes.ActivityId = Activities.ActivityID GROUP BY Activity ORDER BY Activity; and the second one is: select Activity, SUM(Amount) as "Total Amount 2008" from Activities...
I am trying to update a column in table a based on whether a different column in the table is in a set of results from table b. Currently variations on: update a set a.field1 = case when exists ( select b.field2 from b where b.field2 = a.field2 ) then 'FOO' else 'BAR' end are not running. Any ideas how to do this for a ...
My database isn't actually customers and orders, it's customers and prescriptions for their eye tests (just in case anyone was wondering why I'd want my customers to make orders less frequently!) I have a database for a chain of opticians, the prescriptions table has the branch ID number, the patient ID number, and the date they had the...
I am new to SQL and relational DBMS. I want to retrieve records from a relational database if they satisfy 3 given properties. For example, if there is a table containing info about solids: table_solid : |s_id|s_name|description| table_width : |w_id|w_name| table_height: |h_id|h_name| table_length: |l_id|l_name| where *_id are all pr...
Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance or otherwise ? Does it differ between different SQL implementations ? ...
In management studio you can see the owner under properties but it won't let you change it. My guess is there's some stored procedure to change it and you can't do it through the gui. ...
Image you are creating a DB schema for a threaded discussion board. Is there an efficient way to select a properly sorted list for a given thread? The code I have written works but does not sort the way I would like it too. Let's say you have this data: ID | ParentID ----------------- 1 | null 2 | 1 3 | 2 4 | 1...
I have implemented the "MOD 10" check digit algorithm using SQL, for the US Postal Service Address Change Service Keyline according to the method in their document, but it seems I'm getting the wrong numbers! Our input strings have only numbers in them, making the calculation a little easier. When I compare my results with the results fr...
I have data in a MSSQL table (TableB) where [dbo].tableB.myColumn changes format after a certain date... I'm doing a simple Join to that table.. Select [dbo].tableB.theColumnINeed from [dbo].tableA left outer join [dbo].tableB on [dbo].tableA.myColumn = [dbo].tableB.myColumn However, I need to join, using different formatting, bas...
I have this query that works fine. It selects rows into the SY.UserOptions table for the ‘Jeff’ user. However, I created another query that I want to do the same thing, but for every user. So I added SY.Users to the query, which in effect mulplies the 2 tables together. However, it gives me an error that I do not understand. -- This wo...