Store All Data In One Coloumn ?
Dec 31, 2006
we need to decide an architecture for Performance on a web site Search!
I wanna use text service of SQL 2005 .But I am worried about the performance ....
How should I design the system if I want the best perfomance and scalability ?
1.Should I build a seperate coloumn in my every table and merge all the information into one coloumn and full text index that column.
2.Put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.
3.Make a different table and put _ID,_TYPE and _VALUE fields and search in that table with less coloumns.
4.Seperate the full text database and search in a seperate db so that I can scale better?
did anybody have a similiar problem ? Any books on full text search ?
View 4 Replies
ADVERTISEMENT
Nov 19, 2007
Hello, Im a very new to SQL server etc, so please bear with me.
I am currently trying to find a quick way of making a large number of database entries within a particular table all have the same value in one particular column.
I have created a query in MS SQL Server Management Studio, which outputs all the entries in a particular table that I want to change. Currently they all have different values in a certain column in this table, and I want them all to have the same value in that column.
How do I do this within Server Management Studio?
Thank you.
View 3 Replies
View Related
Sep 8, 2006
Hellos;
I have a lot of News. Only i want to get last 10 news and piorty is important for me to change orders of the news. So i add a new coloumn in the table (Piorty int) ...
select top 10 NewsID, Piorty, Title, Spot from News order by 1 desc, 2 asc --
i want to get last 10 news than order by piorty (which piorty is bigger is be thre first) but in this query i just get last 10 news desc the piorty isn't imports even though i would get them order by piorty but last 10 news...
How can i do that ??? Please hellppp ... thnank you verry muchhh ...
View 11 Replies
View Related
Mar 1, 2006
I have two tables. One table (Table 1) has a column containing commadelaminated email addresses. The other table (Table 2) has a columnwith just one email address in it. I need to perform a query thatjoins the comma delaminated table (Table 1) to Table 2, when the singleemail address in Table 2, is contained in the list of email addressesin (Table 1).I hope this isn't too cryptic, and I know comma delaminated lists arebad, but I can't do anything about that.I need a select statement that can perform this task. Anyone have anysuggestions?
View 4 Replies
View Related
Apr 15, 2008
hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this:
SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost,
dbo.table2.dialed_digits
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)
please help me its urgent
View 3 Replies
View Related
Oct 22, 2007
I have one table 'tbUser' having coloumnsUserID,UserName,Category UserID UserName Category 1 Shailaja H 2 Anagha H 3 Raju L 4 Kiran L I want output as UserID UserName HigherCategory LowerCategory 1 Shailaja H 2 Anagha H 3 Raju L 4 Kiran L
View 1 Replies
View Related
Apr 28, 2008
I have a table on my form and each row contains data from a query.
I have a 'Total' row at the bottom and want to add up the above rows. Id rather not do this in the query itself.
Is it possible to do it from the layout view?
View 6 Replies
View Related
Jun 5, 2007
Hi All,
I am struck at one point. I am trying to this operation and not able to go further.
1. I have got the dataset to a variable in the control flow.
2. I am looping through the dataset based on a coloumn.
3. Now inside my For each loop i have a dataflow task.
4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @othervariable.
Now my question is
Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.
Thanks in advance..
Regards,
Dev.
View 15 Replies
View Related
Apr 11, 2007
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
View 6 Replies
View Related
Oct 20, 2007
hi my friends;i have got a problem.i thing you can help mea='x80x02}qx00(Kx02Kx03Kx04Kx06Kx05Kx07u. 'i want to store database but i don't know which encoding and dataytpepls help mesorry for my bed english...thanks all
View 3 Replies
View Related
Aug 18, 2015
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies
View Related
Jan 1, 2007
Hi
I have this stored procedure using Table Variable.
-------------------------------------------------------------------------------------------------------------------------------
create procedure sp_matching @pid Int
ASdeclare @tbl table (MAID int, image varchar(100))
insert @tblselect MAID, image from productswhere pid in (select top 10 mid from matching1 where pid=@pid order by newid())
select * from @tbl
GO
---------------------------------------------------------------------------------------------------------------------------------
When I run this sp, i have table with 10 rows. But what I really need to do is to store all data in one row like:
PID, MAID1, Image1, MAID2, Image2, ..... MAID9, Image9, MAID10, Image10
How can I do this? Please help me out for this.
Thanks.
View 5 Replies
View Related
Aug 19, 2007
Hi friends I want to add HTML to the SQL server as data please can anyone help me. which data type . & how to store the data. Thanks for all your support Thanks & regardsASPFreak
View 3 Replies
View Related
Feb 15, 2004
Hi all,
please, show me How to data is store on the server ?
thanks for reading.
View 5 Replies
View Related
Jul 23, 2005
Greetings All, I was wondering if any of you would share some of yourexperiences regarding the task of loading a data store from an Oltpsource. We are using Analysis Services in a BI product that requiresdata to be pulled from one of our products, an OLTP database. Thedesign is to first run an ETL process from the OLTP source into anoperational data store, from here Analysis Services will pull its datato do its thing. Now, for small OLTP databases (< 1Gb) the storedprocs I have written to do the extraction works well, it is relativelyfast and efficient. However, we have a few databases that are 10Gb'sand the load could end up taking several hours. During this long loadthe OLTP source may be in use and I want to avoid write blocks, or if Iwere to use "select ... NOLOCK" I could get dirty data brought over. Icould used BCP for some of the big tables or Bulk Copy but I wanted tosee if anyone has dealt with this issue and what their specificresolution was for their specific problem. It is my hope that byseeing how others have dealt with this I will be able to architect asolution for my specific problemRegards, TFD.
View 1 Replies
View Related
Apr 7, 2007
hi,
i learned that BLOB data types are out-row data by default, i think it means it will not store in the row of table, because the max-length of a col is 8KB.
but will the system store those BLOB data? and is there any way to specify where should it be stored, like tha Partition Schema.
i am using SQL Express, so i hope it works for express.(partition does not work for express)
View 5 Replies
View Related
Oct 3, 2007
Hi,
This is more store procedure:ALTER PROCEDURE dbo.InsertSpecialOrders
(@OrderID int,
@DepotName nvarchar(50),@CustomerName nvarchar(50),
@OrderDate nvarchar(50),@TelephoneNumber nvarchar(50),
@ObtainedFrom nvarchar(50),@CustomerCanCollect nvarchar(50),
@DepositPaid nvarchar(50),@PriceQuoted nvarchar(50),
@OrderTakenBy nvarchar(50),@BalancePaid nvarchar(50),
@Status nvarchar(50),@TransferedBy nvarchar(50),@CarriagePrice nvarchar(50)
)
AS
Declare @LatestID Int
INSERT INTO Specials
(
OrderID,
DepotName,
CustomerName,
OrderDate,
TelephoneNumber,
ObtainedFrom,
CustomerCanCollect,
DepositPaid,
PriceQuoted,
OrderTakenBy,
BalancePaid,
Status,
TransferedBy,
CarriagePrice
)
VALUES
(
@OrderID,
@DepotName,
@CustomerName,
@OrderDate,
@TelephoneNumber,
@ObtainedFrom,
@CustomerCanCollect,
@DepositPaid,
@PriceQuoted,
@OrderTakenBy,
@BalancePaid,
@Status,
@TransferedBy,
@CarriagePrice
)
SELECT @LatestID = Scope_Identity()
INSERT INTO SpecialParts
(
OrderID,
PartNo,
Quantity
)
VALUES
(
@LatestID,
@DepotName,
@CustomerName
)
RETURN
Now I want to insert data and create the tables:
SqlDataSource ordersDataSource = new SqlDataSource();ordersDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SpecialsConnectionString1"].ToString();
ordersDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
So to insert data for the first field I need:
ordersDataSource.InsertParameters.Add("@CustomerName", CustomerName.Text) Is this right? Any help is appreciated.
View 2 Replies
View Related
Mar 10, 2008
HI,I anm geting error when i want to store some text which contens single quote like this Hi I am 'santosh'.as i am using text editor which genetates XML data (not pure) so i have used varchar(max) to store the data but it gives error.
Is thier any way to store text with single quote........
urgent plz.
View 1 Replies
View Related
Mar 22, 2008
how to use class iDataReader's function getbytes(...............) to read binary data?
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Data.Common;using System.IO;
public partial class uploadFileToDatabase : System.Web.UI.Page{ string strUser; protected void Page_Load(object sender, EventArgs e) { strUser=Request.QueryString["download"]; if(strUser!=null) { string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; dbComm.CommandText="select * from userFiles2 where fileisn='"+strUser+"'"; IDataReader myReader=dbComm.ExecuteReader(); int strFileLen=0; if(myReader.Read()) { strFileLen=Convert.ToInt32(myReader.GetString(3)); FileStream afs; string fPath; fPath = Server.MapPath(Request.ApplicationPath) + "\upLoadFiles\" + myReader.GetString(1); afs = new FileStream(fPath, FileMode.OpenOrCreate, FileAccess.Write); Byte[] filedata = new Byte[strFileLen]; afs.Write(filedata, 0, strFileLen); BinaryWriter abw; int iBufferSize=1000; Byte[] outbyte=new Byte[iBufferSize-1]; long retval; long starIndex=0; abw=new BinaryWriter(afs); starIndex=0; int itemNum=4; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); //when i store large data into a file it display an error such as "Buffer offset '0' plus the bytes '82452' is greater than the length of the passed in buffer. abw.Write(outbyte); starIndex+=iBufferSize; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); } abw.Write(outbyte); abw.Flush(); abw.Close(); afs.Close(); myReader.Close(); dbComm.Dispose(); } } } protected void Button1_Click(object sender, EventArgs e) { string strFileName; string strFileType; int strFileLen; Random ran=new Random(); string strFileISN=DateTime.Now.ToString("yyyyMMddhhmmss")+ran.Next(0,999); if(FileUpload1.PostedFile.FileName!=null) { strFileName=FileUpload1.PostedFile.FileName; strFileType=strFileName.Substring(strFileName.LastIndexOf(".")+1); strFileName=strFileName.Substring(strFileName.LastIndexOf("\")+1); strFileLen=FileUpload1.PostedFile.ContentLength; string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; string dateTime=DateTime.Now.ToString(); Stream fs=FileUpload1.PostedFile.InputStream; Byte[] filedata=new Byte[strFileLen]; fs.Read(filedata,0,strFileLen); dbComm.CommandText="insert into userFiles2 (fileisn,filename,filetype,filesize,filecontent,uploadTime) values (@fileisn,@filename,@filetype,@filesize,@filecontent,@uploadTime)"; dbComm.Parameters.Add(new SqlParameter("@fileisn", (object)strFileISN)); dbComm.Parameters.Add(new SqlParameter("@filename",(object)strFileName)); dbComm.Parameters.Add(new SqlParameter("@filetype",(object)strFileType)); dbComm.Parameters.Add(new SqlParameter("@filesize",(object)strFileLen.ToString())); dbComm.Parameters.Add(new SqlParameter("@filecontent",(object)filedata)); dbComm.Parameters.Add(new SqlParameter("@uploadTime",(object)dateTime)); dbComm.ExecuteNonQuery(); fs.Close(); dbConn.Close(); dbComm.Dispose(); dbConn.Close(); } } }
View 3 Replies
View Related
Apr 18, 2008
I have a recursive query, using common table expressions, like this:
WITH TaskHierarchy (GUID, ParentGUID, Title, Complete, HierarchyLevel)
AS
(
SELECT GUID, ParentGUID, Title, Complete, 1 HierarchyLevel
FROM Task
WHERE ParentGUID = @GUID
UNION ALL
SELECT t.GUID, t.ParentGUID, t.Title, t.Complete, th.HierarchyLevel + 1 HierarchyLevel
FROM Task t
INNER JOIN TaskHierarchy th
ON t.ParentGUID = th.GUID
)
SELECT (COUNT(*) - SUM(CAST(Complete AS INT))) Outstanding FROM TaskHierarchy
The result is a number. I need access to this number. Ideally, I would like to store it in a variable, but anything would work as long as I can access it after the query.Anyone know of a way?
View 1 Replies
View Related
Sep 13, 2005
Hi,Is it possible for me to store 000 or 01 or 001 in an Integer data type in SQL Server without it dropping the leading zeros?
Thanks
View 2 Replies
View Related
May 2, 2006
Hi
when I store html file with image in image data type of database sqlserver, where will actual data store (content of html file, and file image which display on html file), in which folder
Can I help you
View 4 Replies
View Related
Apr 19, 2006
i want to return certain number of columns frm table using store procedure .and i hav to load those returned data into text boxes which i created in asp.net page.is there a way
View 2 Replies
View Related
Nov 11, 2005
My project is to automate testing of Stored Proceduresof type SELECT (at least for now).I want to create a table where each stored procedure'sinput parameter values are entered and in another tablethe expected result value(s) are entered when executedagainst a sample database containing manually enteredand verified data.My current problem is that the stored procedures' inputparameters range from none to 50 parameters (ok nowthat I think of it maybe this SP with 50 parameters is anINSERT SP; regardless let's assume I have SPs thatrequire 10-20-30+ parameters).My other problem is each stored procedures' returnedresult could fall into any of these four categories:- 1 row, 1 column- 1 row, many columns- many rows, 1 column- many rows, many columnsSo far I thought about 3 ways of storing the data:1- 1 large table with 50 columns that can hold variousnumber of input parameter values. Similar type of table forholding the output result for the four categories above.2- 1 small table holding one value per row. All kinds of joinswith other tables to indicate which SP and which column thevalue belongs to...3- 1 individual table per stored procedure, this way the numberof columns in the table would match exactly the number of inputparameters.Obviously the above 3 categories could apply both for the inputand output data.Now I'm still in research mode and I haven't decided on any choiceyet. And I know each approach has serious consequences; let's justsay they are all with some limitations.Has anyone dealt with this scenario before? This is the first time Ineed a table to hold various types of returned data.Just to give some numbers: possibly a few thousand SPs and verylikely each SP would have more than one test scenario.What would you suggest?Thank you
View 2 Replies
View Related
Jun 8, 2006
i am developing one custom transfer component, where i am building one custom object and want the same to be transfered from ComponentUI to component.I explored in this issue and came to know that we can make use of SaveToXML and LoadXML methods of IDTSPersist90 interface. The problem is i could not able to make use of this interface.If any body faced same issue and got the solution, let me know the same.
Thanks in advance
Karun
View 1 Replies
View Related
Jan 29, 2006
I have xml string that needs to be stored in a field in the DB. I was looking for recommendations for the data type I can use in such a scenario.
View 1 Replies
View Related
Dec 6, 2007
Is it possible to do something like this?
SELECT * from (
exec Store_Procedure
)
--StartDate is a column from "Store_Procedure"
where StartDate >= @Param
View 3 Replies
View Related
Sep 13, 2006
How can you loop over a data reader and save the current row to a temp array of some sort and return this array back to the caller?
SqlDecimal total = 0;
SqlConnection conn = new SqlConnection("Context Connection=true");
SqlCommand cmd = new SqlCommand(@"SELECT column1, column2, column3
FROM table ", conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr.GetSqlString(1) == "01")
{
// ADD the current record to a temp list
total = total + rdr.GetSqlDecimal(2);
}
}
SqlContext.Pipe.Send(total.ToString());
// RETURN the temp list
rdr.Close();
conn.Close();
View 4 Replies
View Related
Jun 15, 2007
Hi all ,
What datatype should I take to store time in a table -- datetime , float or decimal?
my requirement is to store "Worked Hours in a day by an employee" in the field say, 9 hrs and 30 mins.
I should be able to manipulate data in this field such as total hours present in the month, extra hours worked in a day (considering 9 hrs as standard time),less hours worked in a day, and so on
View 4 Replies
View Related
Oct 4, 2007
I have a .Net database application that we've successfully deployed in a connected environment. Now we have a client that has the need to store data on a central SQL Server and publish that data out to tablet PC's that will be able to disconnect from the central SQL Server. At some point those tablet PC's will come back in and connect to the central server via VPN and will need to push their changes back to the server. Some fundamental questions:
1) Am I correct in assuming that replication is the best way to accomplish this?
If so,
2) Which replication type sounds appropriate to the above scenario?
3) Am I correct in assuming that the tablet PC's will need some version of SQL Server to support editing of the data in the disconnected state?
My perhaps incorrect first take on this was that we could use a licensed SQL Server on the central server and SQL Server Express as a replication subscriber on the tablet PC's.
Any guidance greatly appreciated!
View 1 Replies
View Related
Jun 22, 2007
Hello everyone!
So I am trying to wrap my head around the most efficient way to store some game data in sql server 2005.
I wont list all of my tables or columns as there would just be too many, but will try to post as exact as I can.
Basically lets say I have 3000 monsters in my game, and over 8000 items some of which are monster loot.
Each monster could have 1 drop item or they could have 200 different item drops all depending on the monster.
So I am just trying to find the best method of creating a loot table.
So I have the following in mind.
[MonsterTable]
monsterID;
monsterName;
etc...
[ItemTable]
itemID;
itemName;
etc..
[LootTable]
lootTableID;
refMonsterID;
refItemID;
So I'm not worried about how I would get all of the data in there, I'm just wondering if this simple method of using Ref. ID's between tables would provide the best method for extracting the data when needed.
Any ideas or thoughts are more then welcome, any questions just ask, and thanks for any guidance or information you can provide on a more efficient manner.
View 1 Replies
View Related
Jan 9, 2007
hello,
I want to store the photo in the sql server 2005 , what datatype should I use?
View 5 Replies
View Related
May 9, 2006
Do data mining results store at some files or in the database? I need the mining results at later time for reporting purpose.
View 1 Replies
View Related
