I'm having problems with handling a very large amount of user records - about 100.000 - 150.000 records.
Instead of selecting all of them at a time, how do I f.ex. select 1000 of them? (f.ex. get nr. 1 - nr 1000, then get nr. 1001 - nr. 2000) ???
I have 5 records in a table called "testing" as such:
LetterCdcontact_id
x1
x2
x3
x4
x5
How can I split this dataset so that three records have LetterCd = 'y' and the other 2 records have LetterCd = 'x'? The number of records can vary, but the idea is the same: split the values in the LetterCd column evenly (50% = 'x', 50% = 'y'). LetterCd is varchar(25) and contact_id is int. I can't update the data, it would have to be a SELECT statement with some sort of CASE statement to get the remainder of the record count?
Hi all:I restored one backup database (7.9 GB mdf) on two diffrent servers. Ishrunk them by clicking "Move pages to beginning of file beforeshrinking".After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. Ishunk again and again:1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times)2. the 4.2Gb become 4.0GB (just one more time)It is wired, I am wondering the mdf will be smaller and smaller if Icontinue to shrink them? What is the reason?ThanksWJ
View 1 Replies View RelatedI have a situation where I need to migrate data from an older platform to a newer one. The data from the old system(s) will be available on DAT tapes. All database construction on the new system will be identical to the old one in size and schema, except for one table (call it "ARCHIVE").
If the ARCHIVE table on the old system is 210MB, and the ARCHIVE table on the new system has the same attributes but has been expanded to 380MB in size, can I simply restore the dump for the old table into the new ARCHIVE?
Empirically it works (I have done it with apparent success two times) but I seem to recall that backups are done by pages, and I'm concerned that there may be conditions not being met by simply doing the restore the way I'm planning to do it.
Also, are there any tests or checks built into SQL which I can use to check table integrity on the target ARCHIVE table after the restore?
Any help is greatly appreciated.
Best rgds,
Kevin
I have a database in 2 GB .mdf and a 1 GB ldf. The backup is much
smaller. I need to copy this database to another server which does
not have that much free space. Can this be restored to a smaller
.mdf and .ldf? How?
Thanks.
Ranjit
Is it generally or almost always better to have multiplesmall SPs and functions to return a result set instead ofusing a single big 1000+ lines SP?I have one SP for example that is 1000+ lines and earlyanalysis of the SP I see it first has 3 big blocks of codeseparated by IF statements. Then within each IF blockof code I see 3-4 UNIONs. UNIONs that meansthey are all returning the same columns so I amguessing these are prime candidates for becomingindividual functions or SPs, maybe even dynamic SPs.Obviously I am not showing you the code but am Iright to think this way? This same SP has about 15 JOINsincluding some LEFT JOINs and one LEFT JOIN to a (SELECTstatement) and almost all the tables referenced by theseJOINs have thousands of records, very possibly hundreds ofthousands.The SELECT statement is returning 30-40 columns froma lot of the these tables plus I also see a lot of CASE ELSEstatements within the main SELECT statement. The code ofeach CASE statement is calling a function. As an exampleif the CASE is for EmployeeID then a function is being calledto get the EmployeeID's FirstName and LastName. If the CASEis for CustomerID then another function is being called to getthe Customer Name.I am thinking to cut this big SP to many smaller SPs and/or functionsand I also plan on using table variable(s) to hold temporary resultwhile I continue processing the records from the table variablewith other code logic.Also I want to leave as the last thing to do is to convert the"machine result", i.e. EmployeeID or CustomerID to "humanreadable result", i.e. Employee FirstName and LastName,Customer Name.I am trying to test this on the Northwind's Employees table,but the Statistics IO, Time and the Execution Plan aresomething I've only started to use. I am unable to makeconclusion which method is better. I'll work on posting anotherpost specifically with details to this test that I am currently doing.My opinion is that by having 1 single SP with 15+ join causea lot more locking than if I would run smaller SPs and store theresult into temp table variables and continue processing theremaining code logic.I would like to know what you think and if I am right or wrongon how I want to optimize this SP?Thank you
View 5 Replies View RelatedHello there....
I have a scenario where I am trying to set up multiple database instances for multiple test/development environment(s) for each group where the test/dev environment will contain a copy of what was in the production environment.. The test/dev environment can be refreshed on demand based on the prior night's full backup of the production environment.. This is good for our web developers and for training purposes, as the test environment(s) can be played around with, and will retain data for as long as the developers/testers/trainers need it, and then can be refreshed to the most current data when everyone in the group decides they want it refreshed...
Normally, this works out well...
However I am having a file size issue...
The production database was pre-allocated (a long time ago) to a large file size (probably to reduce external fragmentation).... So even though the backup file is only 5GB, the production database file itself is something like 40GB... I believe the production database has a maintainance plan on it already that rebuilds the indicies each weekend, etc...
Anyhow, the problem is that when I restore the 5GB database back into a newly created database file, the file expands all the way up to 40GB again, even though the backup file is 5GB...
Normally this would be fine, the problem is that I am trying to create multiple environments, and I do not have the disk space on my test/dev server for 40GB (plus another 15GB or so for the transaction log) multiplied by each of my test/dev environments... It would be much nicer if I could get this down to 5GB (or heck, even 10GB), since I know for sure that the total amount of data in the database doesnt exceed 5GB, and I have plenty of space on my disk for 5 (or 10) GB multiplied by each of the environments I want to create...
I have tried DBCC SHRINKDB and I have tried DBCC SHRINKFILE with the truncate after the restore, which seems to work but doesn't....
I have also tried to go into the database properties and change the "initial size" but that doesnt do anything etiher
Is there any way to get this file back down to a manageable size after the restore??
Or better yet, is there a special method to restore the database so it wont 'expand' back out to 40GB in the first place??? Perhaps some option to tell the restore process that even though the source database had a 40GB pre-allocation, that the database I am restoring into doesn't need to be pre-allocated??
Alright. I'm stuck. I admit it!
I have a bunch of names, and each name can have one or more 'roles'(operator, reader, key operator, etc. Just random words really.) attached to it.
Using reporting services, I've managed to get the information I need with relative ease... the only problem is, with 900 some records to display, it's current length of 41 pages with just one column going down the left side of each page is not exactly preferred by my superior (can't say I blame him really. Looks kind of odd!)
It looks like this right now:
Name1
Function
Function
Function
Name2
Function
Function
Name3
Function
Function
etc all the way down to page 41
I need it to look something like this:
Name 1 Name 4 Name 7
Function Function Function
Name 2 Name 5 Function
Function Function Name 8
Function Function Function
Name 3 Name 6 Function
Function Function Function
etc. Or some variation of...
I've fiddled around, and merely adding one extra column to the initial table-layout with the same =(!UserName etc) just merely replicates the data in the second column... not giving me the new stuff.
I'm quite new to reporting services, but none of the tutorials I've seen/done seem to accomodate for this... Heeelp!
Hi all,
I found a database file and a log file over 2G on mssql 2000 server. Actually, they only need around 200M. I try to backup, truncate the database in order make the size smaller. But the size cannot be smaller. How can I do it?
Simon
I have a Database A in production with 12GB as data file and 8 GB as log file. How do I restore this db in Development with a smaller log file, say 1GB?
I can't shrink the log file or anything in production. What is the best way to restore in Dev with a smaller log file?
Thanks.
Hello,
I'm looking at using full-text indexing for tables to query. I have some smaller fields (varchar(50) that stores names) that I was contemplating using full-text indexing for. I was just curious if it is worth it?
Basically the data that will be there are one-word names, without any spaces or whatnot.
Brian
Hey guys,
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Thanks,
-Kilka
However when I start SQL 2012 it loads the Management Studio but does not load the smaller splash screen that usally appears asking me to connect to a server. When I try to click any of the menu items at the top of the screen the system just hangs.
I also have 2012 Service pack 1 installed too.My installations of 2005 and 2008r2 still work fine.I also tried loading SQL2014 and had the same issues as with 2012.
To all,How to backup a database into a number of smaller files ?For example, can I can fully backup a DB of 10 MB into 10 files (each 1MB)???The problem I've met is that the DB backup file is too large, over 4GB, and even Winzip can't compress it (after compressing, around 80 %of compression rate is possible)Thanks![color=blue]>From Jason (Kusanagihk)[/color]
View 11 Replies View RelatedHiI have a backup device file ... ".bck" whick has grown pretty large.Is there any way I can reduce its size ?ThanksDavid Greenberg
View 4 Replies View RelatedHi... We have application that connected to MSSQL 2000 database. The database file is getting bigger and bigger over the years. Recently the performance of the database and application is getting slower and slower, my senior oracle DBA told me that I should archive the MSSQL 2000 data file and export the old record to the archive DB. So that the number of record will be less and it will be faster.
I would like to know whether MSSQL 2000 support archiving of the database file ? If yes, what is the way to do it ? I could not find it in Enterprise manager option at all.
I have a table that I filled with data imported from another database.
What I need to do is now take this huge table and break apart the information and put it into 5 smaller tables.
So I have a huge insert statement.
I have one main table called Property with two keys. One key is a "Prop_ID" and the other is "owner" where Prop_Id is a automated unique ID. Once the information is inserted into that table, I then get the Unique ID that it was given, and I then used that ID to insert into the other tables.
The problem I am encountering is I keep getting the following error
Violation of PRIMARY KEY constraint 'PK_Prop_Res_Detail'. Cannot insert duplicate key in object 'Prop_Res_Detail'.
The statement has been terminated.
I have an idea what might be going wrong, but I am not sure. What I want to happen is that I want the query to look at the first row of the huge table and then do all 4 of the inserts, and then go to the next row. But I think it is trying to all the inserts into the property table, and then go on to the Prop_Res_Detail table and that is why I am getting that error.
Any help is greatly appreicated.
here is the code..
Code:
CREATE PROCEDURE [dbo].[Insert_Properties]
AS
DECLARE @Prop_ID Int
SET NOCOUNT ON
INSERT INTO Property(Acres,
Assoc_Phone,
Assoc_Cell,
AppraisalForm,
Area,
Assess_Account,
AttachDetach,
Block,
City,
County,
Directions,
DOM,
ER_EA,
FloodZone,
Import_From,
Import_ID,
Insert_Date,
LandSQFT,
LandSQFTDim,
LegalRemarks,
ListAppraiser_ID,
ListAssoc_ID,
ListBroker_ID,
ListDate,
Listing_Office_Remarks,
ListPrice,
Lot,
Map,
Num_Images,
Office_Phone,
Original_ListPrice,
Owner,
Pending_Date,
PhotoName,
PropSubType,
Prop_Type,
Quad,
Remarks,
State,
Status,
StreetDir,
StreetNum,
StreetName,
Township,
UnitNumber,
ZipCode)
SELECT CONVERT(FLOAT(8), Acres),
CONVERT(Varchar(25), Assoc_Phone),
CONVERT(Varchar(25),Assoc_Cell),
CONVERT(Varchar(50), AppraisalForm),
CONVERT(Varchar(10), Area),
CONVERT(Varchar(50), Assess_Account),
CONVERT(Varchar(20), AttachDetach),
CONVERT(Varchar(20), Block),
CONVERT(Varchar(40), City),
CONVERT(Varchar(50), County),
CONVERT(Varchar(1000), Directions),
CONVERT(int, DOM),
CONVERT(Varchar(10), ER_EA),
CONVERT(Varchar(50), FloodZone),
CONVERT(Varchar(20), Import_From),
CONVERT(Varchar(20), Import_ID),
CONVERT(datetime, Insert_Date, 101),
CONVERT(Varchar(20), LandSQFT),
CONVERT(Varchar(50), LandSQFTDim),
CONVERT(Varchar(2000), LegalRemarks),
CONVERT(Varchar(50), ListAppraiser_ID),
CONVERT(Varchar(50), ListAssoc_ID),
CONVERT(Varchar(50), ListBroker_ID),
CONVERT(varchar(11), ListDate),
CONVERT(Varchar(1000), Listing_Office_Remarks),
CONVERT(Varchar(10), ListPrice),
CONVERT(Varchar(20), Lot),
CONVERT(Varchar(10), Map),
CONVERT(Varchar(10), Num_Images),
CONVERT(Varchar(25), Office_Phone),
CONVERT(Varchar(10), Original_ListPrice),
CONVERT(Varchar(50), Owner),
CONVERT(datetime, Pending_Date, 101),
CONVERT(Varchar(50), PhotoName),
CONVERT(Varchar(25), PropSubType),
CONVERT(Varchar(20), Prop_Type),
CONVERT(Varchar(10), Quad),
CONVERT(Varchar(1000), Remarks),
CONVERT(Varchar(25), State),
CONVERT(Varchar(10), Status),
CONVERT(Varchar(4), StreetDir),
CONVERT(Varchar(15), StreetNum),
CONVERT(Varchar(50), StreetName),
CONVERT(Varchar(20), Township),
CONVERT(Varchar(6), UnitNumber),
CONVERT(Varchar(20), ZipCode )
FROM Imported_Closed_Property_From_MLS
SET @Prop_ID = @@Identity
/*Property Res Table */
INSERT INTO Prop_Res_Detail(Prop_ID,
Addition,
Appliances,
Basement_Area,
BasementDesc,
Builder,
Construction,
Cool,
Dining,
District_School,
Energy,
Exterior_Features,
Fence,
Floors,
Foundation,
FP,
FP_Type,
Garage_Attach_Detach,
Garage_Cap,
Handicap,
Heat,
HOA,
HOA_Fee,
HOA_Inc,
HOA_Period,
Inlaw_Plan,
Interior_Features,
Livestock,
Lot_Desc,
Mechanical,
NumLivingArea,
Num_Baths,
Num_Beds,
Num_Levels,
Other_Info,
OvenDesc,
Owner,
Parking,
Patio,
Patio_Dim,
Perc_Basement_Com,
Pool,
Pool_Type,
Prop_Faces,
Range,
RangeDesc,
Remodeled,
Rental,
RentalAmount,
Roof_Type,
Roof_Year,
RoomOther,
Sect,
SQFT,
SQFTSource,
Style,
Tax_Amount,
Tot_Rooms,
UtilityAvailable,
WindowType,
Year_Built)
SELECT @Prop_ID,
CONVERT(Varchar(50), Addition),
CONVERT(Varchar(100), Appliances),
CONVERT(Varchar(25), Basement_Area),
CONVERT(Varchar(100), BasementDesc),
CONVERT(Varchar(50), Builder),
CONVERT(Varchar(50), Construction),
CONVERT(Varchar(20), Cool),
CONVERT(Varchar(10), Dining),
CONVERT(Varchar(60), District_School),
CONVERT(Varchar(100), Energy),
CONVERT(Varchar(100), Exterior_Features),
CONVERT(Varchar(40), Fence),
CONVERT(Varchar(100), Floors),
CONVERT(Varchar(40), Foundation),
CONVERT(Varchar(50), FP),
CONVERT(Varchar(40), FP_Type),
CONVERT(Varchar(50), Garage_Attach_Detach),
CONVERT(Varchar(25), Garage_Cap),
CONVERT(Varchar(20), Handicap),
CONVERT(Varchar(20), Heat),
CONVERT(Varchar(40), HOA),
CONVERT(Varchar(30), HOA_Fee),
CONVERT(Varchar(100), HOA_Inc),
CONVERT(Varchar(20), HOA_Period),
CONVERT(Varchar(20), Inlaw_Plan),
CONVERT(Varchar(100), Interior_Features),
CONVERT(Varchar(40), Livestock),
CONVERT(Varchar(400), Lot_Desc),
CONVERT(Varchar(100), Mechanical),
CONVERT(Varchar(10), NumLivingArea),
CONVERT(Varchar(5), Num_Baths),
CONVERT(Varchar(5), Num_Beds),
CONVERT(Varchar(30), Num_Levels),
CONVERT(Varchar(100), Other_Info),
CONVERT(Varchar(100), OvenDesc),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), Parking),
CONVERT(Varchar(25), Patio),
CONVERT(Varchar(50), Patio_Dim),
CONVERT(Varchar(25), Perc_Basement_Com),
CONVERT(Varchar(20), Pool),
CONVERT(Varchar(20), Pool_Type),
CONVERT(Varchar(40), Prop_Faces),
CONVERT(Varchar(20), Range),
CONVERT(Varchar(100), RangeDesc),
CONVERT(Varchar(50), Remodeled),
CONVERT(Varchar(10), Rental),
CONVERT(Varchar(10), RentalAmount),
CONVERT(Varchar(20), Roof_Type),
CONVERT(Varchar(5), Roof_year),
CONVERT(Varchar(100), RoomOther),
CONVERT(Varchar(10), Sect),
CONVERT(Varchar(10), SQFT),
CONVERT(Varchar(50), SQFTSource),
CONVERT(Varchar(100), Style),
CONVERT(Varchar(10), Tax_Amount),
CONVERT(Varchar(5), Tot_Rooms),
CONVERT(Varchar(100), UtilityAvailable),
CONVERT(Varchar(50), WindowType),
CONVERT(Varchar(5), Year_Built)
FROM Imported_Closed_Property_From_MLS
/*Sold Info Table */
INSERT INTO Sold_Info(Prop_ID,
Buy_Pts,
Closed_Date,
Closed_Price,
Closed_Price_SQFT,
COOP_Sales,
Days_On_Market,
InterestRate,
Lender,
LoanAmount,
LoanTerms,
Loan_Years,
Origination_Fee,
Owner,
SellerConcessions,
LoanType,
Sold_Remarks)
SELECT @Prop_ID,
CONVERT(Varchar(10), Buy_Pts),
CONVERT(datetime, Closed_Date, 101),
CONVERT(Varchar(10), Closed_Price),
CONVERT(Varchar(50), Closed_Price_SQFT),
CONVERT(Varchar(50), COOP_Sales),
CONVERT(Varchar(5), DOM),
CONVERT(Varchar(10), InterestRate),
CONVERT(Varchar(50), Lender),
CONVERT(Varchar(10), LoanAmount),
CONVERT(Varchar(50), LoanTerms),
CONVERT(Varchar(10), Loan_Years),
CONVERT(Varchar(10), Origination_Fee),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), SellerConcessions),
CONVERT(Varchar(25), LoanType),
CONVERT(Varchar(1000), Sold_Remarks)
FROM Imported_Closed_Property_From_MLS
/*Remarks Table */
INSERT INTO Remarks(Prop_ID,
App_Date,
App_Remark,
Contract_Date,
Inspection_Type,
Owner,
PendingSalesPrice,
PendingSaleComments)
SELECT @Prop_ID,
CONVERT(datetime, App_Date, 101),
CONVERT(Varchar(1000), App_Remark),
CONVERT(datetime, Contract_Date, 101),
CONVERT(Varchar(50), Inspection_Type),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(10), PendingSalesPrice),
CONVERT(Varchar(1000), PendingSaleComments)
FROM Imported_Closed_Property_From_MLS
GO
I'm self-taught on T-SQL so forgive me if this is a dumb question. If I have a stored procedure containing the following...
IF [condition]
SELECT ...
ELSE
SELECT ...
Would it be more efficient/proper/etc. to break those two SELECTs out into their own SPs and execute them from the IF statement?
IF [condition]
EXEC [SP]
ELSE
EXEC [SP]
I've got a number of pretty lengthy SPs that I think are pretty tight but if I can make them more efficient by breaking them down into smaller tasks, I'd rather do that.
thanks!
Ron Moses
I've got two queries that I'm using, but I would like to know if I could combine them into one query.
SELECT MAX(ENTRY_ID) FROM MYDB.CALLS
.......
SELECT * FROM MYDB.CALLS WHERE ENTRY_ID = ?(result from previous query)
as always I appreciate any help.
thx
What is the most efficient standalone .NET class that can hold a single disconnected record? The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.I'm familiar with DataSet, is that the only .NET-standard class to do this? Thank you,Shannon
View 7 Replies View Relatedi am using web parts with drag and drop feature , i did not use aspnetdb for user membership
while it is published it gives eror as follows
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
Please give me ur valuable advice , thanks in advance
Hi!
Mgmt just decided that the designer (me) was more valuable than the developer (now me). I hope there are some gurus out there who can help.
What would be the best way to duplicate certain parts of my RS? Suppose
I have a table with three columns: ID, adName, and priority. When I serve the ads, I am randomizing them, but would like to serve some more frequently than others. I am hoping there is a single query I can use, that will seed the RS with multiple instances of high-priority records.
Should I stick to play-doh?
I am still using SQLv 6.5 and have a need to do monthly reports. It is my understanding that there is no inherent capability in SQL v6.5 for functions such as MONTH getdate() as exist in SQL 2000. Does anyone know of a script that will simulate this so I can extract information from a database such as date_last_update IN MONTH 02?
Thanks
Dale
I'm very familiar with Oracle at a conceptual level and need tounderstand MSSQL as well. Can anyone point me to a document/web pagethat describes the different files used in MSSQL and a discussion of howthey relate? (ex. with Oracle, there is tempdb, redo, archive, db files,rollback, etc.)
View 4 Replies View RelatedWhat is your opinion using Sub Selects versus Joining Tables?
SELECT (SELECT COMPANY_NAME
FROM COMPANIES
WHERE COMPANY_ID = c.COMPANY_ID) AS 'Company Name'
FROM COMPANY_ORDERS c
vs
SELECT co.COMPANY_NAME AS 'Company Name'
FROM COMPANIES c, COMPANY co
WHERE c.COMPANY_ID = co.COMPANY_ID
I'm not having any problems, just curious what everyone is
practicing and is "system-actically" more resourceful. I
personally like using Sub Selects (or Sub Queries) so this way I
wouldn't have to deal with complex joins and where conditions,
especially when your just dealing with a releational table.
Hallo, i need some help
I've done this script
SET NOCOUNT ON
DECLARE @login varchar(50)
DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)
OPEN cur
FETCH NEXT FROM cur
INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
But the result is not what i originally wanted.
It's generates multiple select tables with only one row.
I need only one table with all the rows generated.
I tried this:
SET NOCOUNT ON
DECLARE @login varchar(50)
DECLARE cur CURSOR FOR
select distinct user
from products with (nolock)
OPEN cur
FETCH NEXT FROM cur
INTO @login
IF @@FETCH_STATUS = 0
BEGIN
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
WHILE @@FETCH_STATUS = 0
BEGIN
union
select last,first from users where login=@login;
FETCH NEXT FROM cur
INTO @login
END
CLOSE cur
DEALLOCATE cur
But it ended in an error on the union command.
Can you help me?
Thanks and sorry for my bad english :)
Debiru
This is my statement:
SELECT TOP 10 * FROM Team_Roster ORDER BY P_Goals DESC
This is what I get:
Place Number Player Name Team Year Position Goals
1 44 Tyler, Bret Brunswick 2004 Webmaster 17
2 29 House, Tanner Bonny 0 5
3 25 Dee, Robby Cheverus 0 4
4 7 Marshall, Jeffrey Cheverus 0 3
5 27 Ramsey, Travis Biddeford 0 3
6 3 Thompson, Joe Brunswick 1960 Wired 3
7 5 VanDyk, Josh Cheverus 0 2
8 3 Dimmen, Jeff Cheverus 0 2
9 79 Danis-Pepin, Simon Cheverus 0 2
10 45 Oinker, Jeremy Biddeford 1999 Wonderboy 2
11 98 Good, Wicked Brunswick 2007 Lefty 2
12 21 Duffy, Matt Biddeford 0 2
What the heck is going on?
See the following list of: rows desired vs. rows returned
SELECT TOP n
When n=1, actual number of rows returned = 1 etc.
" 2 " " 2
3 3
4 12
5 12
6 12
7 12
8 12
9 12
10 12
11 12
12 12
13 16
14 16
15 16
16 16
17 27
Any ideas?
how can i make this stored procedure:
INSERT INTO tableX (field1, field2, field3) VALUES (value1, value2, value3)
where value1 is field11 in table11
and value2 is field22 in table22
and value3 is field33 in table33
so all values are stored in another tables
I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:
table Company, fields CompanyID, CompanyName
table Address, fields AddressID, AddressName
table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID
CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:
Select c.CompanyID, c.CompanyName, a.AddressName
FROM Company c
LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID
LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID
WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))
There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.
Is there any way to optimize this query, or is it a problem with the base table implementation?
Any advice would be appreciated.
We are using the Transfer SQL Server Objects Task to transfer a large table. The trans log is filling up for this table. Is there a method to split the Data Transfer Task into smaller batches? (Smaller tables are transferring without issue.)
Thanks.
Hi everyone,
I want to get year, month, day and time parts of a DateTime field in sql,
Can help me?
Hi, gurus:
Please tell me some third parts monitoring tools for MS SQL server 2000.
The issue is that I need to delete parts of this string using SQL Server.This string is a title of a document which should contain only the Document Category & Title. Instead, it is displaying extra parts such as Year, Date Created, etc. I would like to truncate/parse off the title. The database is Acme_system. The name of the table is dbo.documents, the column name for the title is "title" and there is also column is "childcat".
The title string reads:
2013-730-DAILY NOTES AND TRACKING FORM-11-16-13 700-030714
I want to take off everything except for "DAILY NOTES AND TRACKING FORM-11-16-13 700"