What Is The Differences Between Having And Where Clause In MS SQL 2000or MS SQL 2005 ?
Mar 11, 2008Can I know about the above answer with 1 or examples if anybody can ?
View 6 Replies
ADVERTISEMENT
Differences Between SQL 2005 Thats Comes With Vs2005 And SQL 2005 Standalone ??
Nov 2, 2005I just want to run a webiste off my home computer with a dynamic address.It seems I can do all I want with the SQL that comes with vs2005 except being able to backup the DBIs the SQL 2005 that comes with vs2005 good enoh for everything I should want to do?? Is there some chart somewhere that explains the differences ?
View 4 Replies View RelatedDifferences Between SQL Server 2005 X64 Vs X86?
Mar 12, 2007Hi there,
I'm trying to find what are the main differences between SQL Server 2005 64-bits and 32-bits. So far, I've found some articles about TPC-C performance but I would like to know some response time or execution time of a batch or SSIS packages.
Any information about this 2 versions is appreciated.
Thanks!
Connection String Differences In SQL 2005
Apr 15, 2008Hiya,
I'm a SQL n000b, and have just joined the forum 5 minutes ago seeking an answer to this problem that I've encountered. It seems that the way connection strings are handled in SQL 2005 have changed. This connection string used to work, but is now borked:
<add key="DBNAME.ConnectionString" value="packet size=4096;integrated security=SSPI;data source='sqlvirdev0035dev35';persist security info=False;initial catalog=DBNAME;connection timeout=60" />
Has something explicitly changed in SQL 2005 that would break this <add key>? Does something need to be set server side in order to handle these connection strings now? I have no idea why this won't work anymore, I just know that it doesn't work.
Ty for any help,
~Chipley
Differences Between MS SQL Server 2000 And 2005?
Jan 20, 2006Firslty, my aplogies if this is documented elsewhere - I am a new user to SQL Team and not yet found everything! I hope someone here can help me...
In brief, I am making the foolish mistake of embarking on my third and final year of an MS(c) degree in forensic computing. For my final year project I am intending to study and document (for forensic computing purposes) the forensic capture and investigation of data from a MS SQL Server database.
However, my experience is mostly from MySQL! In other words, I know very little about the internal structure of MS SQL Server 2000 or 2005.
Which leads me to my question....
Can anyone point me in the direction of a technical pagedocumentpdf (whatever) that details what the core fundamental differences are between 2000 and 2005. I'm not talking about an MS publicity paper - no, I need a non-bias technical guide which states the differences as fact.
If the differences in 2005 are mostly just cosemtic (the GUI etc) then I'll study 2000 because lots is already known and documented about it it seems. However, if it's much more than that and the differences are specific to what I'm studying (the forensic capture) then I'll probably have to go with 2005 because that's what we will be encountering more of in the next few years and the differences will effect the investigator.
Your time and responses much appreciated.
Regards
Ted
(BTW - Having looked at the description of 'Inside Microsoft SQL Server 2000', it seems like it might be a good book for my project (if I do 2000). Would you guys agree?)
www.f3.org.uk
Differences Betwee 2000 And 2005 SQL
Jul 9, 2007Hi,
I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:
ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT
PP_PERIOD_ID,
CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC
The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help
- T.A.
What Are The Differences Between MS SQL Server 2005 && Oracle 9i ?
Aug 5, 2006Hi guyz,
I have basic knowledge of Sql Server 2005 and now i wanna move ahead in Oracle 9i !
So, i have 2 questions here -
a)Whats the Difference between Sql Server 2005 and Oracle 9 i ?
Note: Please keep the discussion general so that student like me can understand. I have never seen Oracle but the industry requirements suggest that Oracle is way better than than SQL Server 2005. But thats what i think
b)I am Running Windows XP SP 2 and i would like to practise Oracle 9i. So, Where i can download it for free ? I know i have checked there website but they don't mention the difference between each version. Oracle does't market their products as well as Microsoft.
Please , I am Student .. so i request you to make the discussion general .
Thank you for your time.
UPDATE Statement Differences Between 2000 And 2005
Jun 21, 2007I just wanted to post a difference I found between SQL 2000 and SQL 2005 regarding UDPATE statements that are done on a join. I understand that if tables are designed correctly this won't be a problem. But, when you inherit a bad design, you are unfortunately stuck with it. Hopefully this will help ease data differences in your migration from SQL 2000 to SQL 2005.
Run this code on a SQL 2000 connection, then run on SQL 2005. My guess on the behavior difference is strictly performance based since 2005 pulls the top result. Either way it can cause a lot of head scratching if you're not aware of it.
IF OBJECT_ID('tempdb..#UpdateTestA') IS NOT NULL
DROP TABLE #UpdateTestA
IF OBJECT_ID('tempdb..#UpdateTestB') IS NOT NULL
DROP TABLE #UpdateTestB
CREATE TABLE #UpdateTestA(
UpdateTestA int identity(1, 1),
FullName varchar(20),
UpdateData varchar(10))
CREATE TABLE #UpdateTestB(
UpdateTestB int identity(1, 1),
FullName varchar(20),
UpdateData varchar(10))
INSERT INTO #UpdateTestA(
FullName)
VALUES ('Barney Rubble')
INSERT INTO #UpdateTestB(
FullName,
UpdateData)
VALUES ('Barney Rubble', 'First')
INSERT INTO #UpdateTestB(
FullName,
UpdateData)
VALUES ('Barney Rubble', 'Second')
SELECT * FROM #UpdateTestA
UPDATE a
SET a.UpdateData = b.UpdateData
FROM #UpdateTestA a
INNER JOIN #UpdateTestB b on b.FullName = a.FullName
SELECT * FROM #UpdateTestA
DROP TABLE #UpdateTestA
DROP TABLE #UpdateTestB
Hope this solves a problem that you were having too.
View SELECT Differences Between SQL 2000 && 2005
Apr 8, 2008I have the following a view on a SQL2K box that uses the following SELECT statement:
SELECT
SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)
The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.
My question is why this works in SQL2K and not SQL2K5?
Thanks,
Kevin
Merge Replication Differences Between SQL 2000 And SQL 2005
Jun 27, 2006We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.
Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.
We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.
For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).
To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.
Examples of the current behaviour are as follows,
On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.
Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.
This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.
Everything still points to the merge replication setup.
So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.
Sorry for the convoluted post. Hope someone can help us.
Cheers
Steve
Differences Between 2005, 2000, And Express - Documentation, Whitepapers?
Apr 27, 2007Can anyone point me in the direction of some NON-sales documentation on the differences between these product? I am sure, especially with Express, there are considerable functionality and architecture differences.
I've looked in BOL, and I've done searches online.
All i seem to get is sales related stuff.
I'm curious about the architecture of SQL 2005, SQL express. In 2000, there was some fairly detailed documentaiton on this subject, but 2005 BOL seems REALLY diffucult to find things.
I may just need to try different keywords...
What Are The Differences Between CD1 And CD2 Of The SQL Server 2005 Enterprise? And Can I Just Attach The Sql2000 MDF File Into Sql2005?
Feb 4, 2006Hi
I have new bought the SQL server 2005 enterprise, but it have 2 CDs, so what are the differences between CD1 and CD2? and so which one should i install first? or is it necessary to install both two or just need to install one of them?
And about my original sql 2000 database, can i just attach it's MDF file into the sql 2005 engine, or which import wizard can load the sql 2000 MDF into sql 2005? or do i need to keep the sql 2000 engine before do this?
thx
MS SQL 2005: Where Clause And Optimizer
Feb 1, 2007Way back when, and at least in version 7 IIRC, the query optimizer gaveup when the where clause in a statement contained more than 4 searchconditions.Does anyone know if such a limitation still exist in MS SQL 2005? TheBOL seems to be silent on the issue.Boa
View 2 Replies View RelatedORDER BY Clause Does Not Work In SQL 2005?
Feb 6, 2006Hi,
A quick background on the problem;
My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place.
I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance.
Except....
None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio).
So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT...
If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code.
If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever.
To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED.
I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days!
Can anyone help?
Thanks always in advance,
Drew
Is This A Known SQL Server 2005 T-SQL Bug With Sub-selects In An IN Clause
Feb 17, 2008Try the following code in SQL Server 2005:
Code Snippet
CREATE DATABASE test_bug
GO
USE test_bug
GO
CREATE TABLE test1 ( a int )
CREATE TABLE test2 ( b int )
GO
INSERT INTO test1 VALUES ( 1 )
INSERT INTO test2 VALUES ( 2 )
GO
SELECT * FROM test1
SELECT * FROM test2
GO
-- This should error but instead returns all rows from test1
SELECT * FROM test1 WHERE a IN ( SELECT a FROM test2 )
I've tried it onw three different boxes that I have access to.
SQL Server 2005 Is Rtrim Needed In Where Clause?
Aug 13, 2007When is RTRIM needed in a Select ... where clause. I noticed that if I have a column named TEXT varchar(17) which is varchar and in the where clause I state where
TEXT = 'This is the text'
or I state
TEXT = 'This is the text ' followed by 4 spaces
The equate still works - so when do I need RTRIM?
Do I need RTRIM for a host variable:
...where TEXT = RTRIM(:VAR_001)
if the host variable is the same length as the TEXT column field in the SQL Server 2005 database?
SQL 2005 Vs SQL 2000: Where Clause Short-Circuiting?
Oct 12, 2006Hello all, this is my first post, and I think I've found something interesting between sql 2005 and 2000. Thanks in advance for any help you can provide.
I am attempting to migrate a system from SQL 2000(v 8.00.2039 - Enterprise Edition) to SQL 2005 (v9.0.2047 - Standard Edition ), and a few of my most complex queries are generating errors. I was able to isolate the problem to a fairly simple query that works in SQL 2000, but generates an error in SQL 2005.
I believe that the problem has something to do with how short-circuiting is handled in the WHERE clause. I'm not sure if short-circuiting is really going on when these statements are parsed, but it seems to describe the situation well.
To replicate the problem, first create the following table and fill it with values:
----------------------------------------------
create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))
insert into orgview (orgname, qid, answer) values('org1', 120, '1')
insert into orgview (orgname, qid, answer) values('org2', 94, '2006-06-06')
insert into orgview (orgname, qid, answer) values('org3', 98, 'free kevin')
----------------------------------------------
The second value (org2) contains a date, but the rest do not.
Under both systems, the following contrived query works fine:
SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
It would seem, that when QID = 94, the expression short circuits, and the CONVERT statement doesn't run. This is good, because the CONVERT statement would throw an error whenever 'Answer' doesn't contain a date.
However, if modify the query by adding an OR to the end like this:
SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
or QID=98
then the query fails on SQL 2005 with a 'Conversion failed when converting datetime from character string.' error.
On SQL 2000, the short circuiting works fine, and the above query returns 'org2' and 'org3'.
I know shortcircuiting may not be the best way to describe the set calculus going on in the background, but can anyone explain what is going on here? Is there any way to make SQL 2005 work like SQL 2000 in this specific case?
Thanks,
-Adam
Top Clause In SQL Server 2005 Compact Edition
Oct 19, 2007
Hi
Can we use Top Clause in the Select statement while using SQL Server 2005 Compact Edition. If not, is there any other workaround available to achieve same results.
Regards,
Salman Shehbaz.
SQL Server 2005 Performance - Where Clause Range Retriaval
Sep 28, 2007Everything is flowing smoothly for the SQL Server Database I have, except one type of retrieval and that is when the where clause has a range of data values to do the retrieval then the performance is terrible. I cannot anticipate every range. There are indexes on the table to try to help; however, nothing seems to help. Has anyone had a similiar problem? Any suggestions to improve performance?
Thanks, Mary
INSERT INTO OPENROWSET Does Not Respect ORDER BY Clause On SQL Server 2005 EE
Jul 3, 2007Hi,
I need to pass data from a SQL Server data base to an Access data base. To do this I use the OPENROWSET as followed:
FR
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:Aux.mdb'; 'Admin'; '',Test) (Id, Name, TypeId) SELECT Id,Name,TypeId
FROM Test
ORDER BY TypeId
FR
On SQL Server 2000 or MSDE the data is transfered as expected, respecting the specified order. But when I run the same clause on a SQL 2005 EE the data is transfered, but the order is not respected.
So my question is if I have to activate an option for the order to be respected or if this is a bug.
Best regards,
Ângelo Vilela
Should We See Replication Filter Where Clause Text In Profiler TextData On SQL Server 2005?
Sep 25, 2007
We have Merge Replication publications for SQL Server 2005 Compact Edition subscribers.
Some articles have filter statements that send rows to multiple subscribers, based on the value of Host_Name() supplied at run-time.
Our publications work for most subscribers, but we have at least one subscriber who downloads too many rows from one of the filtered tables.
When we run the Select SQL from the article's Filter statement it returns the intended 4 rows for this subscriber.
We cut and pasted the filter statement into query analyzer, substituted the subscriber's value for Host_Name(), executed the statement, and got the proper 4 rows for this subscriber in the results.
But when this subscriber syncs her Compact Edition database it downloads 10 rows - the proper 4 rows that the filter statement should pass, plus 6 other rows that she should not download.
Our hypothesis is that the Filter statement is not properly applied to the article when this subscriber syncs.
Other subscribers get the proper rows when they sync, so the publication's filter statement works in some cases, for some values of Host_Name().
We'd like to see the application of the filter statement at run-time (sync-time), but we have not found the text of the filter statement in SQL Profiler output. Should we expect to see the text of the filter statement in SQL Profiler output?
Is there a better way to debug this error?
FYI, here's the text of the article filter statement:
SELECT <published_columns> FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = Host_Name())
end
)
)
And here's the statement we ran from Query Analyzer:
declare @id varchar(10)
select @id = 'aultnc'
SELECT * FROM [dbo].[TBL_USER] WHERE user_sys_id in (
select u.user_sys_id
from tbl_user u
join tbl_territory t on u.territory_gid = t.territory_gid
where t.terr_no_id like (
select
case (select t.data_access_qnty from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 2 then (select t.terr_no_id from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
when 3 then (select left(t.terr_no_id,5)+'%' from tbl_user u join tbl_territory t on u.territory_gid = t.territory_gid where u.user_sys_id = @id)
end
)
)
Thanks
Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause
Nov 4, 2015I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
SQL 7.0 And SQL 6.5 Differences
Nov 22, 1998I am curious to what major differences there are between these two versions.
Trying to decide whether or not to purchase the SQL 6.5 training kit from Microsoft or not. If the code and utilities are the same, then I could probably still learn from the 6.5 version. Any thoughts, suggestions will be greatly appreciated.
Thanks
What's The Differences?
Aug 12, 2004What is the differences between SQL Server Desktop Engine and SQL Server Standard Edition???
Thanks
Lystra
SQL CE 3.01 And 3.5 Differences
Feb 13, 2008Hi,
I am having the following problem AFTER converting to VS2008 from VS2005 and SQLCE 3.5 from 3.01:
SQL CE db file has a table called Court0 with various columns of type float. I populate the values by copying floats from another table/tables. I do this via ado.net using this code snippet:
foreach (DataColumn column in this.mycourtsDataSet1.Tables[tableName].Columns)
{
string columnName = column.ColumnName.ToString();
string columnValue = aRow[0][columnName].ToString();
object cValue = aRow[0][columnName];
if (columnName.Remove(1) == "T" && !string.IsNullOrEmpty(columnValue))
{
// Add the value to the Court0 table.
DataRow[] bRow = this.mycourtsDataSet1.Tables["Court0"].Select("BookingPeriod = '" + columnName + "'");
if (bRow.Length > 0)
{
double colValue = Convert.ToDouble(cValue);
//bRow[0][tableName] = Convert.ToInt32(columnValue);
========> bRow[0][tableName] = colValue; <==== colValue is '1055.01'
}
}
}
}
This works fine in VS2005/CE3.01 BUT not in VS2008/CE3.5
In CE3.5, the value entered into the cell looses it's decimal value.
For example, '1055.01' becomes '1055.0' in CE3.5 .
Can someone explain to me why the conversion stuffs up in CE3.5 and what do I do to fix it.
Thanks,
Inner Join Differences
Jul 4, 2006Table struct (table1 and table2):
areacode
phonenumber
phonenumber2 (combined areacode + phonenumber) (actual column)
------------------------------------------------------------
select x.areacode, x.phonenumber from table1 as x
inner table2 as y
on x.AreaCode = y.AreaCode and x.phonenumber = y.phonenumber
result: 0
select x.areacode, x.phonenumber from table1 as x
inner join table2 as y
on x.phonenumber2 = y.phonenumber2
result: 100
select (x.areacode + x.phonenumber) as phone from table1 as x
inner join table2 as y
on (x.areacode + x.phonenumber) = (y.areacode + y.phonenumber)
result: 0
WHat's the difference between those queries? Why can't I get a result from the 1st and 3rd query?
Database Differences
Feb 5, 2004We have a database that when an update is released (and this is very often) the release notes don't cover most of the actual changes. Every time groups of our custom queries and reports get broken due to database changes. Does anyone know how to compare two databases and get a report of the differences between them? I can either have the two versions on the same server or on different servers if that makes a difference.
I'm hoping for something where you input @oldversion, @newversion
and return is
@oldversion, tblname, fieldname, char(8)
@newversion, tblname, fieldname, varchar(8)
@oldversion, tblname, [Null], [Null]
@newversion, tblname, fieldname, int
@oldversion, [Null]
@newversion, tblname
also any changes in dependancies
Thanks
Brent
Rounding Differences
Feb 20, 2004I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding
SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up
Does this make sense?
Differences Between Dbs On Different Servers
Jul 7, 2003as promised:
--sp_addlinkedserver @server = '____________'
--sp_addlinkedserver @server = '____________'
--select * from sysservers
--sp_addlinkedserver
-- '____________',
-- 'Oracle',
-- 'MSDAORA',
-- 'ORC1'
--Select * from ___________.ORC1.dbo.sysobjects
/* Objects in Company1 Missing in Company2 */
Select 'Table Objects in Company1 but are not in Company2'
select Left(a.name,30), a.refdate from sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From ____________.dbname.dbo.sysobjects b where a.name = b.name)
/* Objects in Company2 Missing in Company1 */
Select 'Table Objects in Company2 but are not in Company1'
select Left(a.name,30), a.refdate from ____________.dbname.dbo.sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From sysobjects b where a.name = b.name)
/* Column Differences */
Select 'Column Differences between like named tables'
select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
, Left(x.DataType,15) as Company1DataType, x.length as Company1Length, x.refdate as Company1RefDate
, Left(y.DataType,15) as Company2DataType, y.length As Company2Length, y.refdate as Company2RefDate
from
( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U' and a.name like 'TBL%') As x
, ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
where a.id = b.id and a.xtype = 'U'
and b.xusertype = c.xusertype
and a.name like 'TBL%') As y
Where x.TabName = y.TabName
and x.ColName = y.ColName
and (x.length <> y.length or x.DataType <> y.DataType)
/* Column Differences */
Select 'Column in Company1.com not in Company2'
Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from ____________.dbname.dbo.sysobjects d, ____________.dbname.dbo.syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2
/* Column Differences */
Select 'Column in Company2 not in Company1.com'
Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from sysobjects d, syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2
--Select 'Table Objects that are still in use in both Company2 and Company1'
--select Left(a.name,30), a.refdate from sysobjects a, ____________.dbname.dbo.sysobjects b
--where a.name = b.name and a.xtype = 'U'
Brett
8-)
Differences Between .sqlexpress And C:abc.mdf
Apr 2, 2008
I saw some demo-codes ,introducing sqlconnection class, sqlcommand class and etc, are involed .sqlexpress and c:abc.mdf.
so , I am quite confuse what's the deferences between .sqlexpress and c:abc.mdf
Thanks
Temp Table Differences
Jun 2, 2005What's the difference between using CREATE TABLE #TempTable and DECLARE @Table TABLE for temp tables and are there any advantages or disadvantages to using one over the other?Thanks
View 4 Replies View RelatedQuery Run Time Differences
Mar 14, 2002I have a server with two test instances of a data base. I have a query which creates a temp table, inserts 29 rows, perform 4 update queries to add counts and then dumpps out the results. This entire query script runs 1.33 minutes on one instance and 2.5 minutes on the other. On the production server this query now runs in 9 seconds. If I run any one of the test updates individually they execute under 2 seconds, just like the production server.
THe results are repeatable.
All are SQL 7 with all service packs on NT4 sp6. Both test data bases are backups of production from last week. I suspect some kind of caching/buffer problem, but I do not know what to look for. I am not a DBA so I have no idea what role TEMPDB plays may play in this.
Can anyone give us ideas on where to look for the performance difference? Will our impending upgrade to SQL2K solve this problem or make it worse? Any ideas would be appreciated.
Is There Any Tool To See The Differences In Two Databases.
Mar 29, 2002Hi,
Is there any tool to find the differences between the two databases. I would like to know the differences in developmental server and Production server. if the developers create any new objects, I want to migrate them to production server.
Can we do it in sql server 200 or do we need to have separate tool.
Thanks in advance.