Optimising A Table With Lots Of Boolean Fields
Jul 17, 2006
I have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.
I would like to construct queries against each of the individual digital channels or combinations of them.
M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.
The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.
In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.
View 5 Replies
ADVERTISEMENT
Feb 7, 2006
What's the best practice for adding / editing a record into a database with lots of fields ?I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.
Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.
But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand, with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...
What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).
With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.
Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.
So come on guys (and gals)... let's hear your thoughts on how you would handle the insert / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)
View 1 Replies
View Related
Nov 12, 2007
I am designing a table where the object(s) that the table represents could have hundreds of boolean attributes. Which method of design would you chose for this scenario:Keep the booleans in the original object's table, potential for hundreds of nulls in a rowCreate a 2 more tables, one that has the boolean value names & ID. Another that relates an object (in original table) to a boolean value name/ID. No nulls, lots of joiningSo second method would probably normalize it, but I would suffer a performance cost, whereas the 1st method would be the easiest/quickest for joins but tons of null records. ThanksBen
View 3 Replies
View Related
Feb 15, 2004
Hi
This may seem amazing and a stupid question but:
Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?
View 2 Replies
View Related
Jan 17, 2008
Hi all,
I have two tables within the SQL server and there is an application writes 20.000 or 40000 rows in the First table.
1) I need to know when the table completes filled to start move the data to the second table.
2) After I copy the rows I need to delete the rows from the first table.
I though to use the trigger but I am not sure if this is the best approach or not
Please help....
Thank you
sms
View 11 Replies
View Related
Mar 19, 2008
Hi,
I am working on an application to analyse down time on a production line system. The system has about 40 rows inserted per minute. The inserts are coming from about 10 different stations.
I need to a analyse the downtime between each insert from each station. The plan is to copy the data to another database on a different server so as not to affect the live system that is being updated by the production line.
However the initial requirement was to do this at night while the production line was down but now they want the data to be updated every 3 hours which means performing this huge query while the production line is bombarding the DB with inserts.
I am wondering what is the best way of doing this. Is there any way I can limit the abount of processor this proceedure will take.
Any advice appreciated,
Thanks,
Sean
View 2 Replies
View Related
Feb 9, 2007
Hi,
I'm new to reporting services and this is a very general question. I'm working on a large sales stats report with many results.
I want to be able to compare many results for two dates. These results
include, average sales value per day, average sales per weekday, sales
with payment received, etc.
So basically there is lots of analysis
needed mainly based on one database table (a fairly standard orders table).
What seemed the most logical thing to do is get all the relevant order rows for
these two date ranges, A and B, and append a period column to the
results, and then do all the maths/aggregate functions in Reporting
Services. Thus only having to connect to database once.
And use a matrix with date period columns.
So my query gives me results like:
Period order_total, is_weekday, no_weekdays_in_period.....
A 123 0 22....
A 54 1 22....
B 134 0 20...
Does this make the most sense? Or should I do the maths (grouping and aggregate functions) in lots of
different queries (in which case, is Reporting Services worthwhile using?)?
Any advise/suggestions appreciated.
View 1 Replies
View Related
Oct 11, 2006
Hello,
I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.
'eligibility (dbo)' table
- Unable to create index 'PK_eligibility'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Any ideas on how to make the table change more efficient or change the timeout period. I need to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.
Thanks!
View 7 Replies
View Related
Apr 15, 2008
I would like to break a large table into multiple pages when previewing in the designer. Any ideas?
View 10 Replies
View Related
Dec 3, 2006
I was wondering what should be used for a yes/no/ entry identifier . Should it be a simple int , tinyint , or uniqueidentifier?
Thank you
View 5 Replies
View Related
May 21, 2005
I try to insert VB.NET boolean true values into a SQL2000 table thru a stored procedure. While varchar fields of the table are correctly filled, I'm surprised to find the bit fields all stay 0 after every single insert. This makes me wonder if VB.NET boolean variables can be passed to sp bit parameters and inserted into a SQL2000 table. Or did I miss something? Please advise. Thanks.
View 1 Replies
View Related
Feb 29, 2008
I wanted to test how SQL server 2005 works with Boolean values.
so I created this table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BoolTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Operand1] [bit] NULL,
[Operand2] [bit] NULL,
CONSTRAINT [PK_BoolTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I populated it like so
insert dbo.booltest (operand1, operand2) values (1,1)
insert dbo.booltest values (1,0)
insert dbo.booltest values (1,null)
insert dbo.booltest values (0,0)
insert dbo.booltest values (0,null)
Then I tried this
select case when operand1 OR operand2 then 'True' else 'False' End
from dbo.booltest where id=1
thinking that since row 1 has 1 and 1 I'll get 'True' as a string showing in the output window
but instead I got this error
Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.
question:
how do you work with bit value table Fields as booleans in a boolean test
many thanks in advance for your time/help
CarlitoA
View 7 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Feb 16, 2008
Ok so i've got a database containing a table called Quote.
I need one of the field's datatype to be Boolean?
which option do i choose?
and also is there a way to make the Key Field auto increment?
And is the datatype: ntext, the correct option for a text only field?
thanks
View 6 Replies
View Related
Feb 8, 2001
Hi ,
We are using a stored procedure which processes more than 11 million records .
The Time that the Stored Procedure takes to execute is around 15 to 20 days .
This is bad . We are not using any cursors , But Delete , Insert & Update Statements . There is some complex where clause also while performing deletes and updates .
Our job is to fine tune the SP . We run into problems like transaction log fillups , Tempdb full etc... U can imagine the problems when u look at the record count ..
Indexes donot help .
Can anybody recomend ways to fine tune the proc.
One More thing we do cross database updates ,inserts ,& deletes (I mean 2 databases in same server).
Bye
NAvin
View 2 Replies
View Related
Feb 8, 2001
Hi ,
We are using a stored procedure which processes more than 11 million records .
The Time that the Stored Procedure takes to execute is around 15 to 20 days .
This is bad . We are not using any cursors , But Delete , Insert & Update Statements . There is some complex where clause also while performing deletes and updates .
Our job is to fine tune the SP . We run into problems like transaction log fillups , Tempdb full etc... U can imagine the problems when u look at the record count ..
Indexes donot help .
Can anybody recomend ways to fine tune the proc.
One More thing we do cross database updates ,inserts ,& deletes (I mean 2 databases in same server).
Bye
NAvin
View 2 Replies
View Related
Jan 8, 2003
Hi,
I'm looking for tips, advice, best practice etc. on optimising a DB with over 300,000 user records to be accessed rapidly via a web interface. Any help would be greatly appreciated - specifically i'm looking at the different methods of DB optimisation indexing, clustering etc.
View 2 Replies
View Related
Apr 30, 2008
Hi all,
I have 20 SQL jobs thst are scheduled to run from say every 5mins to others that run every hour.
Does anyone know the best way to optimise these jobs to run.
At the moment once these jobs are running I cannot browse any tables in teh DB. I get a locked timeout rquest exceeded..
Do I need to stagger when the jobs run.
Or make one big job where they all run one after another ?
Any help ?
Ray..
View 3 Replies
View Related
Mar 26, 2008
Hi there
Recently our company purchased a product from ip2location.com; a database containing 2.9million IP address ranges, and their approximate cities/countries of registration.
Naturally, I thought - "Hey, wouldn't it be great if we could cross reference this with our IIS logs so we could see where our visitors are from?".
So, I set about doing just that. Our IIS logs are already in SQL.
The trouble is, the ip2location database is so large that executing a query against it to find which range a particular IP address is within takes me 1 second. Multiply that by 1,000,000 log rows, and Houston - we have a problem.
One of the issues is that each record in the ip2location database comprises a FROM_IP and TO_IP range to describe a range of IPs. So to find which IP range a particular IP resides in, I have to join using a BETWEEN statement (or so, I think anyway!).
Does anyone have any suggestions on how to improve this process, or has anyone done anything similar before?
Ideally, I'd like to write a trigger to grab the IP region data (i.e. City/Country) and update the IISLog with that value when the new row is inserted, saving me having to do it later.
I tried this, and the batch import of IIS logs into SQL took so long I got bored and gave up :)
Any help anyone can offer would be appreciated.
Many thanks
Richard.
P.S. Somebody is bound to ask - "Why couldn't you just use Google Analytics?"; my answer is because we want to slice up our log data into chunks, and give it to our customers in semi-real time. Plus the logs report on other services - not just HTTP. ;)
View 18 Replies
View Related
Jul 23, 2005
Hi,I have a problem I would really appreciate help with. I am generatingdynamic SQL and need to optimise it. The specific example I am trying tooptimise looks like this:SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)AND (Data LIKE 'SIDE BY SIDE%' )) ))AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CCT3WHERE T1.DataHeaderID = CCT3.DataHeaderID AND (( Data LIKE 'church%' ))))I was OK optimising it with just 2 criteria and changed:SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2WHERE T1.DataHeaderID = CT2.DataHeaderID AND (( Data LIKE 'church%' ))))which took 26 seconds to using a derived tableSELECT distinct T1.DataHeaderID FROM TB_DataDetailText as T1inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailText )CT2on T1.DataHeaderID = CT2.DataHeaderIDWHERE(T1.EntityFieldID IN ( 31) AND (T1.Data LIKE '12BORE%' ))and (( CT2.Data LIKE 'church%' )) which took 0.03 seconds on the same data.My problem is I need to write code to generate the SQL for 1 to n criteriaand am struggling to write the query for more than 2Best regards,Andrew
View 3 Replies
View Related
Mar 23, 2006
Hi.Maybe I'm just being dim, but I'm struggling to get my head aroundoptimising a query with regard to indexes. If I make a select query, suchas a pseudo-example 'select * from bigtable where foo='bar' and(barney>rubble and fred<flintoff)', and the table is indexed on 'foo', howcould I make that any better? What indexes could I add, or what could Ichange in the query?I know it looks simple, but so am I.CheersChris Weston
View 5 Replies
View Related
Jul 28, 2006
Dear All, Plz help me in optimising the following query,Reduce repeatable reads from the table via select ,ythe table sare nothaving referntial integrity constarints ,relationsCREATE proc Rolex136SyncasDECLARE @date varchar(50),@ydate varchar(50)print CONVERT(char(11),(GETDATE()-1),100)SET @date =substring(CONVERT(char(11),(GETDATE()),100),5,2)+' -'+substring(CONVERT(char(11),(GETDATE()),100),1,3) +'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4) SET @ydate =substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)Print @datePrint @ydateinsert intobiiod.dbo.data_trans_currentday_test(MobileNo,UA,M essageID,ContentID,Description,MusicLabel,CPID,CPN ame,ContentType,Category,SubCategory,TransactionDa te,Units,Unitprice,Shortcode,Servicecode,OperatorI D,CatID,SubCatID,SpecialPackage,Royalties,Operator,Circle,OPGPName)(select mobileno,(SELECT CASE uawhen 'unknown' then nullelse uaend) as ua,(select case remarkswhen 'unknown' then nullelse remarksend) as remarks,contentid,(select case descriptionwhen 'unknown' then nullelse descriptionend) as description,(select musiclabel from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as musiclable,(select cpid from datalogs.dbo.contentprovider where cpname =datalogs.dbo.translogs.cpname) as cpid,cpname,contenttype,(select catname from datalogs.dbo.cont_Catg where catid in (selectcatid from cont_master where contentid =datalogs.dbo.translogs.contentid)) as category,(select subcatname from datalogs.dbo.cont_subCatg where subcatid in(select subcatid from cont_master where contentid =datalogs.dbo.translogs.contentid)) as subcategory,transactiondate,1 as Units, price,(select case servicenamewhen 'AIRTELIVE' then remarkswhen 'ALCOMBOPACKREG' then remarkswhen 'HINDI' then remarkswhen 'NOKIAGAL' then remarkswhen 'SUDOKU' then remarkswhen 'SUDOKU_APP' then remarkselse NULLend) as SHORTCODE,servicename,(select case servicenamewhen 'TSTTNEWS' THEN 600when 'TSTTWAP' THEN 600when 'TSTT_MMS' THEN 600when 'AKTEL' THEN 300when 'TELEMOVIL' THEN 700when 'COMCEL' THEN 701when 'QATAR2900' THEN 1ELSE(select operatorid from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as operatorid,(select catid from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as catid,(select subcatid from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as subcatid,(select specialpackage from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as specialpackage,(select Royalties from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as Royalties,(select case servicenamewhen 'AKTEL' then 'Aktel'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'TeleMovil'when 'COMCEL' THEN 'COMCEL'when 'TSTTNEWS' then 'TSTT'when 'TSTTWAP' then 'TSTT'when 'TSTT_MMS' then 'TSTT'when 'ALCLICKWIN6464' then 'Airtel'when 'ALMMSPORTAL' then 'Airtel'when 'ALMMSSMSDWN' then 'Airtel'when 'ALMYALBUM646' then 'Airtel'when 'HINDU6397' thensubstring(remarks,1,PATINDEX('%.6397.%',remarks)-1)else(select OPname from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as Operator,(select case servicenamewhen 'AKTEL' then 'Bangladesh'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'El Salvador'when 'COMCEL' THEN 'Gautemala'when 'TSTTNEWS' then 'Trinidad'when 'TSTTWAP' then 'Trinidad'when 'TSTT_MMS' then 'Trinidad'when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +6,len(remarks)-PATINDEX('%-%',remarks))else(select Circlename from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as Circle,(select case servicenamewhen 'AKTEL' then 'Aktel'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'TeleMovil'when 'COMCEL' THEN 'COMCEL'when 'TSTTNEWS' then 'TSTT'when 'TSTTWAP' then 'TSTT'when 'TSTT_MMS' then 'TSTT MMS'when 'ALCLICKWIN6464' then 'Airtel Click Win 646'when 'ALMMSPORTAL' then 'Airtel MMS'when 'ALMMSSMSDWN' then 'Airtel MMS SMS'when 'ALMYALBUM646' then 'Airtel My Album'when 'HINDU6397' then 'Hindu 6397'else(select OPname from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as OPGPNamefrom datalogs.dbo.translogs where transactiondate >= @ydate andtransactiondate < @date and servicename in('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV', 'QATAR2900','SIFY'))go
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Dec 14, 2004
Hi all
I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.
Simple change to the stored procedure (this is just an example not the real stored proc):
From:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’
To:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’
Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.
Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:
strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.
So I created an index just for the strPostCode (non clustered).
This had no change to the ‘Like select’ what so ever. So I am now stuck.
1)Is there another way to search for part of a text field in SQL.
2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).
Any advice truly appreciated.
View 8 Replies
View Related
Jan 4, 2007
I need to merge replicate data to two different types of subscribers:
Clients subscribers which will have a very small percentage of the data from the central database. The data on these machines will be managed using dynamic filtering on host_name()
Server subscribers which will manage a copy of all the data from the central database
There will be far fewer server subscribers than client subscribers.
As I see it I have two options for the configuration
1) Use two separate merge publications €“ one which is filtered and one which isn€™t
2) Use a single merge publication and setup the filtering so that the server subscribers receive all the rows
Which option is likely to lead to better performance?
With option 1) there would be 2 complete sets of replication metadata which need to be maintained €“ so I am tending towards option 2. Are there any disadvantages in using a dynamic filter to return a very large number of rows?
View 6 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Apr 23, 2008
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
View 7 Replies
View Related
May 6, 2007
In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?
Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts. You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ...." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return3. Should my data source class return data objects like1st approach[DataObject(True)]pubic class TopicDataSource{ public static Topic[] GetTopicList() { }}or should it return DataSet / DataTable / DataReader like2nd approach [DataObject(True)]public class TopicDataSource{ public static DataTable GetTopicList() {}}Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.4. If I go with approach 1, how should I control which property of my data objects is read-only after it's has been inserted/created? Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)? Or do I set it and the page level and write a code to throw an exception in the rare case the application / user try to change it's value? Or else?Please help. These questions slow me down for days now.If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.I will be extremely grateful to anyone who answer any of my questions.Thanks a lot.PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.
View 3 Replies
View Related
Sep 19, 2006
Hello :)
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
View 8 Replies
View Related
Jul 20, 2005
Hello all,I have a database in SQL Server that should save data from a CRM-likeapplication.The database consists of tables like products, services, customers,partners etc. Problem is that the users should be able to find theseitems on different properties and with or without substring finding(SQL: LIKE). Example: I want the users to be able to find a customer,providing a customerID, but also providing a customername, zipcode orjust a part of those strings.This will result in a lot of queries. I bet there are some nicesolutions to this, since I will not be the first with this situation.If anyone can help, please.Thank you in advance.Regards,Freek Versteijn
View 3 Replies
View Related
Aug 31, 2006
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
View 1 Replies
View Related
May 29, 2008
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
View 14 Replies
View Related
