Consider the following simple SQL SELECT statement:
SELECT LastName,MiddleName,FirstName FROM Persons
For the sake of this discussion, let's assume the table name (in this
case "Persons") comes from an outside source. Sometimes that database
has the "MiddleName" property / column, other times it does not.
Under Windows SQL, I'm finding that the query fill fail returing that
it's an invalid query if the requested property / column is not there.
My question is this. I want to make a SQL statement that says return
these columns but skip any columns if they're not present. Is there a
way to do this? Thanks!
Hello all, Am I just missing something here? In SQL Server 2000 EM, I could right-click a table or view, point to Open, then select Return all, Return Top , or Query. In 2005 Management Studio, I right-click the table/view and the only option I query to an UPDATE, INSERT FROM, INSERT INTO, DELETE, etc There is a Query Designer toolbar with a Change Type button on it (disabled though). Is there any way to query a table or view in the Query Design view like in 2000' Studio, like the Query Designer toolbar, untill you close the window) with no toolbars. There is a Query Designer toolbar, but is there a Query Designer in Mngmnt Studio? Why would a toolbar for something that doesn't exist be put in
I have a interesting situation with a table with a nvarchar column that contains a xml string. The average size of this field is about 2100 bytes. This means that I will have few records per page. The primary key (with a clustered index) is in a field not sequential for the clustered index, even at the cost of fragmentation. BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.Thanks Dan, I recreated the PK to May be the deadlocks are related to the way you are accessing the tables. INF: Analyzing and Avoiding Deadlocks in SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;169960 Tracing Deadlocks http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks
anyone has had any issues with backing up a database in SQL Server 2000 and restoring the database to SQL Server 2005 and having a column truncated in one of the restored tables. Very weird. I backed up a LiveStats.XSP database in SQL Server 2000 and restored the database to SQL Server 2005. The app then was trying the table had been deleted. I looked at the original database in SQL Server 2000 and the column was there but was missing upon restore of the database to SQL Server 2005. The column contained NULLs in every record of the table. I'm
I can insert into but can't update a text column - see following example : 1) create a table with a text column CREATE TABLE [dbo].[TestLongText] ( [Id] [int] IDENTITY (1, 1) NOT NULL , [FileContent] [text] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 2) insert text from another table into this table insert into TestLongText(FileContent) select FileContent from OtherTableWithTextCol where Id = 2Try something like: [code] update TestLongText set FileContent = o.FileContent from TestLongText t,OtherTableWithTextCol o where o.Id = 2 and t.Id =1 [/code] Cristian Lefter, SQL Server MVPThanks!! That solved it - i forgot about doing an update based on a join instead of a subquery! *** Sent via Developersdex http://www.developersdex.com ***
I'm trying to create a subset of a table and copy it into another table using select into. For example, select * into cust1 from cust. Now, select distinct acctnum into cust1 from cust brings over only the know how I can do this in tsql? Tx for any help. Bernie YaegerYou can use distinct with a column list: SELECT DISTINCT acctnum, fname, lname INTO cust1 FROM custHi Kalen, Tx for your reply, but it doesn of the cols. (Nevermind that it makes no sense to have different names with the same acctnum - this is just a poor example). Tx for any help you can offer - hey, your articles Foo(accNum,FName,LName) VALUES(12345 ,'bob', 'smith') SELECT accNum,MIN(FName) as Fname , MIN(LName) as LName FROM Foo Group By AccNumHi Allan, Worked perfectly
What is the rule of thumb on indexing a bit column. I have been under the belief that if there a few distinct values in a column ,it's better off non indexed. Is that true? PaulYes, cardinality is a consideration regardless of data type. Even with a bit column, an index might be worthwhile if the overall percentage of qualifying rows to justifies it . and assuming a skew (very few occurrences of one value), one can also consider using a filtered index on SQL Server 2008, like in: CREATE INDEX x ON t(bitcol) WHERE bitcol = 1 Assuming few occurences of 1, and one want this index to help find those rows.Good point, Tibor.Tibor, In my applications a bit column is almost always a 1 with 0 being the exception, .a filtered index is perfect for me thank you very much.
I have a table with a varchar column that the vendor has decided to fill out with either "002" or "2" depending on where the data was collected from. I need to match this on an internal table that has an int "2". So I did this select curr, ISNUMERIC(curr) from tpacn11 The result is a bunch of rows that all say "1" for ISNUMERIC. So then I did this .currId Which fails, saying: Syntax error converting the varchar value '2..' to a column of data type int. So then I tried just collecting data: SELECT CASE WHEN LEN(curr)=1 THEN curr ELSE CAST(curr as int) END as curr
Hi All, We are experiencing an issue using FileStream attribute in the SQL Server tables. If we add a new column in the table before the column which is assigned with FileStream attribute, it removes the FileStream attribute from the column. It is the same behavior if change the position of column that has FileStream attribute assigned. We are not sure if this is a bug in the release or a design constraint and might cause the data loss in future. Any thoughts or help on this are highly appreciated. BizSolutionAre you using the GUI in SSMS or TSQL to do this?
following problem with IN clause (MS SQL 2000): I have 2 tables A and B. Table A contains 805 records. Select count(*) from A returns 805 Select count(*) from A where (select count(*) from B where yy=A.xx)>0 returns 747 Select count(*) from A where xx IN (select yy from B) returns 747, too Select count IN (SELECT yy FROM B WHERE yy IS NOT NULL) Note also that it's generally more efficient to use EXISTS or NOT EXISTS to test for the presence or absence of rows rather than use a COUNT(*) subquery with WHERE