I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
how does one make tempdb smaller, when i try to reduce the allocated size it wont let me? it fell over during a process i have tried to srhink the database in enterprise and tried dbcc shrinkdatabase in analyser but to no avail!
I have a large database currently using up 5GB of space -- I deleted some records of a table and the free space is increased.
So the question is, if I delete some of the data from this table will the database size decrease? Is there anything else I can do to reduce the size of the database? Thank you so very much for any help with this!
I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.
In a ASP application, which used Access and is now being migrated to SQL 2005, I have a medium-sized query with many inner joins. I optimized the tables, with indexes, etc, and if I run the query on the SSMS it returns me like 55 lines in less then a second. On the other hand, when the ASP page passes the query to the SQL 2005, it takes 10 sec. to get the data. Using the Profiler I found that the sp_fetch is being executed a lot of times. It looks like the query is decomposed in smaller pieces and the rows as selected one by one. I'm using OleDb.
How to make the ADO (the culprit in my opinion) to execute that query at once?
We are processing 60,00, 000 rows(2 GB file) available in a flat file and loading them in to a database tables using OLEDB Destination components. In the data pipeline of an SSIS package we have 1 flat file source reader, 7 look up components(full cache mode), 1 multicast component and 2 OLE DB destinations with fast load option.
We have observed that first 10,00, 000 rows are processed and loaded in to target tables in just 4 minutes time. The second set of 10,00, 000 rows are processed in 15 minutes time. After this for processing each 1,00,000 rows SSIS is taking approximately 8 - 10 minutes time. We are not able to identify the reasons for the unexpected behaviour of SSIS.
We thought that as the input file size is 2 GB SSIS is not able to manage and slowing down over time of execution. We did split the big input file in to 60 small 37 MB (approx) size files. Then we modified the package by adding For-Each loop task to process all the 60 small files and load them in to database server sequentially. Even in this approach also we have identified data loading has slowed down drastically after processing 13 files.
In order to verify is there any problem with reading source file or transformation, we have replaced OLEDB destinations component with Flat File destinations. With Flat file destination the time taken for processing rows is very constant. For every 8 minutes package is able to process 10,00,000 rows and write them in to the destination files. So, there is no problem with the with either Look up components or flat file source reader.
We are sure that target database server is in same state/condition from the starting to the end of package execution. The client box in which we are running the package is having 1 GB RAM. During package execution time the CPU usage is at 30 % and PF usage is 580 MB. SP1 is also installed on both Client and Server.
Does any one have clue what is causing slow down of data load over the time of package execution?
HI EVERYBODY This is my procedure " CREATE PROCEDURE SP_SAMPLE_SEARCH @Title nvarchar(256) AS
SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc GO " I exec procedure and it returns 12 results with diffirents ID and want to get these values 1,2,3....12 How do I get these... I am a beginner. Thanks for help..
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005 --i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here? -i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how? cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue; Please help me out with this.Thanks
hi can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
Hi... I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0... cheers.
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?
Why is it my number of records are different from my view. this are the following code that that i used in my code and in my view.
my code in my program: this code show all the records i know
If iConn.State = ConnectionState.Open Then iConn.Close() iConn.Open() Dim rsBills As New Data.DataSet rsBills = New DataSet Dim daBills As New SqlDataAdapter daBills = New SqlDataAdapter rsBills.Clear() Dim cmBills As New SqlCommand cmBills = iConn.CreateCommand ' "Select U_Code, U_Name, U_Level, P_Word From EPassword ", cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no "
With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value. If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter DECLARE@WantedValue INT
SET@WantedValue = 349
-- Stage the source data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, MaxItems INT, CurrentItems INT DEFAULT 0, FaceValue INT, BestUnder INT DEFAULT 0, BestOver INT DEFAULT 1 )
-- Aggregate the source data INSERT@Data ( MaxItems, FaceValue ) SELECTCOUNT(*), Qty FROM( SELECT 899 AS Qty UNION ALL SELECT 100 UNION ALL SELECT 95 UNION ALL SELECT 50 UNION ALL SELECT 55 UNION ALL SELECT 40 UNION ALL SELECT 5 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUP BYQty ORDER BYQty DESC
-- Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT
-- If productsum is less than or equal to the wanted sum, select all items! IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue BEGIN SELECTMaxItems AS Items, FaceValue FROM@Data
RETURN END
-- Delete all unworkable FaceValues DELETE FROM@Data WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
-- Update MaxItems to a proper value UPDATE@Data SETMaxItems =CASE WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue ELSE MaxItems END
-- Update BestOver to a proper value UPDATE@Data SETBestOver = MaxItems
-- Initialize the control mechanism SELECT@RecID = MIN(RecID), @BestUnder = 0, @BestOver = SUM(BestOver * FaceValue) FROM@Data
-- Do the loop! WHILE @RecID IS NOT NULL BEGIN -- Reset all "bits" not incremented UPDATE@Data SETCurrentItems = 0 WHERERecID < @RecID
-- Increment the current "bit" UPDATE@Data SETCurrentItems = CurrentItems + 1 WHERERecID = @RecID
-- Get the current sum SELECT@CurrentSum = SUM(CurrentItems * FaceValue) FROM@Data WHERECurrentItems > 0
-- Stop here if the current sum is equal to the sum we want IF @CurrentSum = @WantedValue BREAK ELSE -- Update the current BestUnder if previous BestUnder is less IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue BEGIN UPDATE@Data SETBestUnder = CurrentItems
SET@BestUnder = @CurrentSum END ELSE -- Update the current BestOver if previous BestOver is more IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver BEGIN UPDATE@Data SETBestOver = CurrentItems
SET@BestOver = @CurrentSum END
-- Find the next proper "bit" to increment SELECT@RecID = MIN(RecID) FROM@Data WHERECurrentItems < MaxItems END
-- Now we have to investigate which type of sum to return IF @RecID IS NULL IF @WantedValue - @BestUnder < @BestOver - @WantedValue -- If BestUnder is closer to the sum we want, choose that SELECTBestUnder AS Items, FaceValue FROM@Data WHEREBestUnder > 0 ELSE -- If BestOver is closer to the sum we want, choose that SELECTBestOver AS Items, FaceValue FROM@Data WHEREBestOver > 0 ELSE -- We have an exact match SELECTCurrentItems AS Items, FaceValue FROM@Data WHERECurrentItems > 0With references to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies
Of course this is not normalized but I can't change this.
Nevertheless I need to get the full number of each Hobby and not only the combination of them.
So instead or in addition to the existing recordset I need e.g
357 Essen which ist the sum of all records containing 'Essen' in the above example
The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.
SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_a UNION SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_b
the problem is the rec_num repeat for each statement like this :
rec_num empname
1 john 2 maggy 1 lee 2 mary 3 louis
How do i make the rec_num continue for the next statement after union.
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a UNION SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a
1 777 Mike HR 2 888 Susy HR 1 111 Smith TECH 2 222 John TECH 3 333 Lenny TECH
How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :
What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
I'm trying to update every record with a incremental number. I wrote the following query but it updates the records with the same number. Could someone please tell me what I'm doing wrong? Thanks.
declare @NextKey int SELECT @NextKey = NEXT_KEY FROM TABLE_KEYS WHERE TABLE_NAME = "tblEmp" set @NextKey = @NextKey + 1
DECLARE tblNewEmp_cursor CURSOR FOR select emp_pk from tblNewEmp
open tblNewEmp_cursor FETCH NEXT FROM tblNewEmp_cursor WHILE @@FETCH_STATUS = 0 begin update tblNewEmp set emp_pk = @NextKey set @NextKey = @NextKey + 1 FETCH NEXT FROM tblNewEmp_cursor end
