http://www.sqlmag.com/authors/author/author/5777647/rsscomment/5777647en-USFri, 25 May 2012 08:56:37 GMTFri, 25 May 2012 08:56:37 GMThttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorFri, 18 Mar 2011 12:33:22 GMT
When you tried the final answer, there is comma missing after the word ’IndexName’.

Vankayala]]>VankayalaFri, 18 Mar 2011 12:33:22 GMThttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-to-a-new-filegroup#commentsAnchorThu, 29 Apr 2010 11:05:54 GMT
it’s probably worth mentioning that doing this for, say, a primary clustered key isn’t very efficient.

BOL mentions using the MOVE TO clause in the ALTER TABLE ... DROP CONSTRAINT statement, although as others such as Deepak have suggestsed, this seems rather pointless.

http://deepakrangarajan.blogspot.com/2008/12/moving-index-to-seperate-filegroup.html

So this ALTER TABLE method seems that it’ll drop the constraint/index and dump the table as a heap onto the new filegroup, then in a second step, the clustered index is re-created.

The more efficient CREATE INDEX with DROP_EXISTING method leaves the structures intact and just re-creates it on the new filegroup. All the NI keep the same keys & you’re not required to drop/re-create all the FKs that reference the table you’re moving.
]]>BORDENThu, 29 Apr 2010 11:05:54 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-to-a-new-filegroup#commentsAnchorhttp://www.sqlmag.com/article/kerberos/passing-credentials#commentsAnchorMon, 28 Dec 2009 09:46:09 GMT
THIS ARTICLE HAS SOME GREAT REFERENCE MATERIAL. I EXECUTED THE RECOMMENDATION WITHOUT SUCCESS. IN THE FUTURE, WHAT WOULD BE HELPFUL IS SNAPSHOT OF ACTUAL SCREEN SHOTS AND COMMENTS AROUND IT...MY PROBLEM IS I DO NOT SEE THE SQL SERVER SERVICES IN THE DELEGATION DIALOG BOX WHEN ENABLED.]]>ARUNMon, 28 Dec 2009 09:46:09 GMThttp://www.sqlmag.com/article/kerberos/passing-credentials#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-to-a-new-filegroup#commentsAnchorThu, 15 Jan 2009 16:31:02 GMT
Hi whiteoak, I passed your question on to the author, Umachandar Jayachandran, who responded by saying "Tables with text, ntext, image, varchar(max), nvarchar(max), varbinary(max), CLR User-defined types, and xml are always stored in the "default" filegroup unless the TEXTIMAGE_ON option is used in the CREATE TABLE. Once any of these columns are added in a table, the data for those columns can only be moved by dropping and recreating the table / columns itself. Rebuilding the clustered index on new filegroup does not affect these columns." Please let me know if you have any more questions. Thanks! Megan Keller Associate Editor, SQL Server Magazine mkeller@sqlmag.com]]>meganbearly Thu, 15 Jan 2009 16:31:02 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-to-a-new-filegroup#commentsAnchorhttp://www.sqlmag.com/article/sql-server/solution-to-february-reader-challenge-check-for-an-empty-table#commentsAnchorWed, 14 Jan 2009 05:04:22 GMT
It’s a good reference]]>EricWed, 14 Jan 2009 05:04:22 GMThttp://www.sqlmag.com/article/sql-server/solution-to-february-reader-challenge-check-for-an-empty-table#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/moving-to-a-new-filegroup#commentsAnchorTue, 06 Jan 2009 15:54:01 GMT
i read on another website that this process will not work if the table has an image or text column type. Is this true?]]>whiteoak Tue, 06 Jan 2009 15:54:01 GMThttp://www.sqlmag.com/article/sql-server-2000/moving-to-a-new-filegroup#commentsAnchorhttp://www.sqlmag.com/article/quering/speeding-up-the-query#commentsAnchorThu, 10 Jul 2008 15:50:38 GMT
I had never really looked at OPENQUERY or hints on joins before, and this article was a great demonstration. I’ve run into these exact same issues many times and it’s good to know a way that might help solve them.]]>WilliamThu, 10 Jul 2008 15:50:38 GMThttp://www.sqlmag.com/article/quering/speeding-up-the-query#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorTue, 03 Jun 2008 15:01:16 GMT
I think it should be something like this: if object_id(’GetCurrentSqlStmt’) is not null drop function GetCurrentSqlStmt go create function GetCurrentSqlStmt (@sql_handle binary(20), @stmt_start int, @stmt_end int) returns varchar(8000) as begin return ( select coalesce(quotename(object_name(s.objectid)) + ’:’, cast( substring( s.text, (@stmt_start/2) + 1, (((case @stmt_end when -1 then datalength(s.text)*2 else @stmt_end end) - @stmt_start)/2) + 1) as varchar(8000)) ) from ::fn_get_sql(@sql_handle) as s ) end go]]>javdvd Tue, 03 Jun 2008 15:01:16 GMThttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorhttp://www.sqlmag.com/article/sql-server/enforcing-uniqueness-without-the-unique-constraint#commentsAnchorWed, 26 Mar 2008 11:13:38 GMT
Hi pavelsommer! Thanks for providing a solution to this Reader Challenge. You can view the solution to this Reader Challenge at http://www.sqlmag.com/Articles/ArticleID/93824/93824.html. Thanks again for taking the time to provide your solution to this problem. Megan Bearly Associate Editor, SQL Server Magazine mbearly@sqlmag.com]]>meganbearly Wed, 26 Mar 2008 11:13:38 GMThttp://www.sqlmag.com/article/sql-server/enforcing-uniqueness-without-the-unique-constraint#commentsAnchorhttp://www.sqlmag.com/article/sql-server/enforcing-uniqueness-without-the-unique-constraint#commentsAnchorWed, 19 Mar 2008 05:09:20 GMT
I think, that Paul should use CHECKSUM(’msg_hdr value’) for msg_id column on insert. Or he can use BINARY_CHECKSUM(-||-) for more uniqueness.]]>pavelsommer Wed, 19 Mar 2008 05:09:20 GMThttp://www.sqlmag.com/article/sql-server/enforcing-uniqueness-without-the-unique-constraint#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorThu, 06 Sep 2007 16:10:46 GMT
Hi mpanarusky, I’m interested in having this sp (InstantDoc #48763) at my company, can you please provide the right function code? Thanks and Regards e-mail:cagoworld@hotmail.com]]>CarlosThu, 06 Sep 2007 16:10:46 GMThttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorMon, 23 Apr 2007 01:36:31 GMT
mpanarusky, can you post the corrected GetCurrentSqlStmt userdefined function on the site. It would be a great help for me.]]>NirmalMon, 23 Apr 2007 01:36:31 GMThttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorFri, 23 Mar 2007 12:08:59 GMT
I made some changes (simplifications) to deal with the compile error reported earlier, but noted that the function was returning half the SQL string when @stmt_end = -1. I corrected this by modifying the CASE statement to double the substring length: "when -1 then datalength(s.text) * 2 else ..."]]>MARKFri, 23 Mar 2007 12:08:59 GMThttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorFri, 23 Mar 2007 11:47:16 GMT
The function GetCurrentSqlStmt does not compile. The error is "Unclosed quotation mark before the character string...", pointing to the single quote at the end of: select coalesce(quotename(object_name(s.objectid)) + ’:’, ’). Please provide a correction.]]>MARKFri, 23 Mar 2007 11:47:16 GMThttp://www.sqlmag.com/article/sql-server-2000/deploying-a-startup-parameter-on-all-servers#commentsAnchorhttp://www.sqlmag.com/article/sql-server/solution-to-february-reader-challenge-check-for-an-empty-table#commentsAnchorWed, 21 Feb 2007 10:04:53 GMT
Why not use SELECT top 1 * FROM instead of Select * from as this will be more efficient?]]>shafiqm@gmail.comWed, 21 Feb 2007 10:04:53 GMThttp://www.sqlmag.com/article/sql-server/solution-to-february-reader-challenge-check-for-an-empty-table#commentsAnchorhttp://www.sqlmag.com/article/migration/running-sql-server-2000-queries-in-sql-server-2005#commentsAnchorThu, 25 Jan 2007 09:09:27 GMT
The solution is in the May 2006 Reader Challenge... Writing Efficient Queries to Return Inventory Items By: Umachandar Jayachandran Reader Challenge InstantDoc #50276]]>DianaThu, 25 Jan 2007 09:09:27 GMThttp://www.sqlmag.com/article/migration/running-sql-server-2000-queries-in-sql-server-2005#commentsAnchorhttp://www.sqlmag.com/article/migration/running-sql-server-2000-queries-in-sql-server-2005#commentsAnchorThu, 25 Jan 2007 02:57:53 GMT
Where is the solution?]]>adore979 Thu, 25 Jan 2007 02:57:53 GMThttp://www.sqlmag.com/article/migration/running-sql-server-2000-queries-in-sql-server-2005#commentsAnchorhttp://www.sqlmag.com/article/kerberos/passing-credentials#commentsAnchorThu, 28 Dec 2006 10:15:30 GMT
does this article contradict what’s written in InstantDoc #23670?]]>mordechai@ibionova.com Thu, 28 Dec 2006 10:15:30 GMThttp://www.sqlmag.com/article/kerberos/passing-credentials#commentsAnchorhttp://www.sqlmag.com/article/sql-server/querying-tables-and-views-on-a-linked-server#commentsAnchorTue, 14 Mar 2006 23:38:23 GMT
Even though this solution is correct from the archetectural point of view, There are few erros in implementing: 1. The base table should be created with SET ANSI_NULLS ON. Otherwise the table shoudl be re-created with these options 2. CREATE VIEW Customers_CompanyCodes WITH SCHEMABINDING is wrong. the view should be created with schema name. 3. Before creating the view, both QUOTED_IDENTIFIER and ANSI_NULLS should be SEt to ON. Another solution that could be considered is adding a computed column and enforce uniqueness on that computed column. Ofcource it will have the implementation cost. Additionally, code like "Insert into NewTable Select * from Customers" will fail as we have an additional column.]]>PreethivirajTue, 14 Mar 2006 23:38:23 GMThttp://www.sqlmag.com/article/sql-server/querying-tables-and-views-on-a-linked-server#commentsAnchorhttp://www.sqlmag.com/article/sql-server/case-sensitive-settings#commentsAnchorTue, 07 Mar 2006 10:15:48 GMT
No info how to change "Case Sensitive Settings"]]>nachtigall Tue, 07 Mar 2006 10:15:48 GMThttp://www.sqlmag.com/article/sql-server/case-sensitive-settings#commentsAnchorhttp://www.sqlmag.com/article/sql-server/enforcing-restricted-uniqueness-on-columns#commentsAnchorMon, 13 Feb 2006 19:25:44 GMT
The above listed solution is incorrect. There exist collations for which the above will not work! First off, we will use SQL Server 2000 to simulate installation of the above table under the SQL_Latin1_General_Cp850_CI_AI collation. This is the Case-Insensitive and Accent-Insensitive variant of CP850. Since the original problem statement only requires case-insensitivity and makes no assertion regarding accent sensitivity, the above collation meets the requirements. To do this under SQL Server 2000: create table meta_FieldNames ( fieldid int not null primary key, fieldname nvarchar(50) COLLATE SQL_Latin1_General_Cp850_CI_AI not null ) This has assigned that particular collation to the fieldname column. Now we use the exact constraint proposed above: ALTER TABLE meta_FieldNames ADD CONSTRAINT CK_FieldName CHECK (fieldname NOT LIKE ’%[^abcdefghijklmnopqrstuvwxyz]%’) And now we go to town: INSERT INTO meta_FieldNames values (1, ’Aaâ’) That should be an a with a caret above it as the third character. No problem. I have now inserted a character with an accent on it into the table. The problem is much more difficult than it appears at first sight. If you take into consideration codepages like SQL_Latin1_General_Cp1254_CI_AS (the Turkish codepage (in which an upper case ’i’ is an ’I’ with a dot on top, and a lower case ’I’ is an ’i’ without the dot!), you will find that many initially obvious solutions fall victim to at least one of the fascinating codepages available! There is a reason my solution involved 156 REPLACE calls, many of them with varbinary parameters. Note that it is an easy problem to solve if you aren’t worried about SQL Server 7.0. If you are only worried about SQL Server 2000, simply an appropriate collation to the column in your DDL and then it’s easy. It’s when you have to deal with a nasty collation that things get difficult. --Toby Ovod-Everett]]>TOBYMon, 13 Feb 2006 19:25:44 GMThttp://www.sqlmag.com/article/sql-server/enforcing-restricted-uniqueness-on-columns#commentsAnchorhttp://www.sqlmag.com/article/sql-server/query-performance-on-partitioned-tables#commentsAnchorFri, 13 May 2005 07:58:13 GMT
The new type of link (sponsored links) are very distracting. I don’t know if its the way they are shown or just the fact that having so many words in different fonts, etc makes reading that much harder. I just know that reading the articles is more of a chore now that these links exist. Thanks]]>Anonymous User Fri, 13 May 2005 07:58:13 GMThttp://www.sqlmag.com/article/sql-server/query-performance-on-partitioned-tables#commentsAnchorhttp://www.sqlmag.com/article/sql-server/os-characteristics#commentsAnchorThu, 14 Apr 2005 17:24:23 GMT
Select @@Version Probably is the SIMPLEST T-SQL code to determine the following OS characteristics: 1. OS version, such as Windows Server 2003 or XP Pro 2. OS edition, such as Enterprise Edition or Standard Edition 3. Service Pack version ]]>Anonymous User Thu, 14 Apr 2005 17:24:23 GMThttp://www.sqlmag.com/article/sql-server/os-characteristics#commentsAnchorhttp://www.sqlmag.com/article/stored-procedures/collation-conflict#commentsAnchorThu, 24 Mar 2005 22:56:39 GMT
Hi, We too tried applying the first solution, setting the collation of the temp table explicitly. But when I do that it always gives an error as ’Incorrect syntax near ’collate’. Can anybody suggest some solution please? Have invested so much time but not able to arrive at a solution. ]]>Anonymous User Thu, 24 Mar 2005 22:56:39 GMThttp://www.sqlmag.com/article/stored-procedures/collation-conflict#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorFri, 11 Feb 2005 22:54:13 GMT
’B,C’? 3rd ret 1 2 not 1 ? ]]>Anonymous User Fri, 11 Feb 2005 22:54:13 GMThttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorhttp://www.sqlmag.com/article/sql-server/avoiding-application-failure#commentsAnchorThu, 10 Feb 2005 14:14:39 GMT
Its Ok ]]>Anonymous User Thu, 10 Feb 2005 14:14:39 GMThttp://www.sqlmag.com/article/sql-server/avoiding-application-failure#commentsAnchorhttp://www.sqlmag.com/article/sql-server/avoiding-application-failure#commentsAnchorThu, 10 Feb 2005 14:14:27 GMT
Nice Article]]>Anonymous User Thu, 10 Feb 2005 14:14:27 GMThttp://www.sqlmag.com/article/sql-server/avoiding-application-failure#commentsAnchorhttp://www.sqlmag.com/article/sql-server/eliminating-recompilation#commentsAnchorFri, 21 Jan 2005 08:39:50 GMT
I am surprised this solution won. Concrete procedure could be rewritten not to use temp table at all, even to use just one select. Temp tables can be replaced by table variables so recompilation will not happen (at least not due to temp table usage). In case of more complex procedures with many branches calling separate procedure from each branch will allow the best execution plan to be used each time and I think it will reduce recompilations in case proc parameter value comparison is part of branching condition.]]>MirkoFri, 21 Jan 2005 08:39:50 GMThttp://www.sqlmag.com/article/sql-server/eliminating-recompilation#commentsAnchorhttp://www.sqlmag.com/article/sql-server/foreign-punctuation#commentsAnchorMon, 17 Jan 2005 12:35:11 GMT
He should dump the contents of the XML into two temp tables (Customers and Orders) and close the handle immediately. Then go into the transaction. ]]>Anonymous User Mon, 17 Jan 2005 12:35:11 GMThttp://www.sqlmag.com/article/sql-server/foreign-punctuation#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorWed, 29 Dec 2004 13:13:45 GMT
you could use dynamic sql or maybe wrap the statements inside an exec to avoid recompilations eg. exec (" statements ")]]>blueoyester Wed, 29 Dec 2004 13:13:45 GMThttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorFri, 10 Dec 2004 16:21:28 GMT
Wow, really nice solutions to the problem. However on my SQL installation GetIdsForAttributes3 won’t compile. Am I missing something? (Line 16: Incorrect syntax near ’)’.)]]>Anonymous User Fri, 10 Dec 2004 16:21:28 GMThttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorThu, 09 Dec 2004 14:16:55 GMT
One easy way out is to use table variable. replace CREATE TABLE #od1 ( OrderID int, ProductID int ) with DECLARE @od1 TABLE ( OrderID int, ProductID int ) If table variable limitations are not an issue this is away to go. Good luck! Dragan]]>Anonymous User Thu, 09 Dec 2004 14:16:55 GMThttp://www.sqlmag.com/article/sql-server/a-dashboard-application#commentsAnchorhttp://www.sqlmag.com/article/sql-server/writing-a-stored-procedure#commentsAnchorFri, 05 Nov 2004 04:21:37 GMT
Great Stuff]]>Anonymous User Fri, 05 Nov 2004 04:21:37 GMThttp://www.sqlmag.com/article/sql-server/writing-a-stored-procedure#commentsAnchorhttp://www.sqlmag.com/article/sql-server/foreign-punctuation#commentsAnchorFri, 10 Sep 2004 02:35:18 GMT
He should add a a line into abort label which provides to remove xmldocument from memory. so the abort label should be : abort: EXEC sp_xml_removedocument @h IF @@trancount > 0 ROLLBACK RETURN 1]]>soulhouseFri, 10 Sep 2004 02:35:18 GMThttp://www.sqlmag.com/article/sql-server/foreign-punctuation#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure#commentsAnchorSat, 14 Aug 2004 00:54:11 GMT
Good One]]>GANESHSat, 14 Aug 2004 00:54:11 GMThttp://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure#commentsAnchorFri, 13 Aug 2004 05:51:10 GMT
Nice Trick, very useful To be continue]]>THIERRYFri, 13 Aug 2004 05:51:10 GMThttp://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure#commentsAnchorhttp://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure#commentsAnchorThu, 12 Aug 2004 13:28:01 GMT
What is incorrect in the query? NC coulmn is width insensitive. So Chnaged the collation type and it should work well. And how can David modify the query to return the correct data without ignoring punctuation or white-space characters in any language? CREATE TABLE #Temp ( nc nvarchar(30) COLLATE Latin1_General_CS_AS_KS_WS ) CREATE CLUSTERED INDEX Idx_Temp_nc on #Temp( nc ) INSERT INTO #Temp VALUES( N’MRKT’ + nchar(0x3000) + N’Name’) INSERT INTO #Temp VALUES( N’TEST String’ ) INSERT INTO #Temp VALUES( N’MRKT’ + nchar(0x3000) + N’Name’ + nchar(0x3000) ) SELECT * FROM #Temp WHERE nc = N’MRKT’ + nchar(0x3000) + N’Name’ + nchar(0x3000)]]>saneesThu, 12 Aug 2004 13:28:01 GMThttp://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure#commentsAnchorhttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorFri, 30 Jul 2004 10:33:46 GMT
What about being able to do this from a central administrative computer? I am already doing almost all this and much, much more by polling all my SQL Server computers and bringing pertinent data into one database, and then creating reports to expose weaknesses, like databases without backup plans, databases without transaction log backup plans, jobs without schedules, and disabled jobs. What I am now trying to do is poll the Stats_Date function, which seems to have to run in the context of the machine from which information is needed. When I run my query, I get all information, but the Stats-Date function returns NULL when it returns the actual value if the same query is run under the source machine context. Any suggestions? Thank you, Neal Neal Graves, DBA Shurgard Storage Centers Seattle, WA]]>NEALFri, 30 Jul 2004 10:33:46 GMThttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorhttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorThu, 29 Jul 2004 08:47:03 GMT
Comment by Glenn about ’CASE’ error. Correct final script by changing "i.name AS IndexName" to "i.name AS IndexName,". There’s a comma missing.]]>jvandeThu, 29 Jul 2004 08:47:03 GMThttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorhttp://www.sqlmag.com/article/stored-procedures/collation-conflict#commentsAnchorMon, 26 Jul 2004 06:59:29 GMT
-]]>gery_plovdivMon, 26 Jul 2004 06:59:29 GMThttp://www.sqlmag.com/article/stored-procedures/collation-conflict#commentsAnchorhttp://www.sqlmag.com/article/tsql3/corrupted-characters#commentsAnchorTue, 08 Jun 2004 03:22:50 GMT
Regarding the January challenge - I have just faced a very similar problem using osql. Another answer - and simpler in my view - is to use isqlw in batch mode. As long as you specify -i and -o parameters isqlw will run without a gui and will actually execute the script. A -F{U/A/O} switch is available to set the format of the input files and it works like a dream. Example call isqlw -i "ANSI.sql" -o "OutANSI.txt" -FA -S Server -d Database -E isqlw -i "OEM.sql" -o "OutOEM.txt" -FO -S Server -d Database -E isqlw -i "UNICODE.sql" -o "OutUNI.txt" -FU -S Server -d Database -E See topic "isqlw Utilty" in SQL Books Online for full details]]>Perry Dyball Tue, 08 Jun 2004 03:22:50 GMThttp://www.sqlmag.com/article/tsql3/corrupted-characters#commentsAnchorhttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorMon, 07 Jun 2004 15:30:16 GMT
I tried the final answer and got the following error: Incorrect syntax near the keyword ’CASE’. All I did was cut and paste into QA.]]>gMon, 07 Jun 2004 15:30:16 GMThttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorhttp://www.sqlmag.com/article/sql-server/trading-stocks#commentsAnchorWed, 12 May 2004 08:19:36 GMT
Excelent]]>Malinda Dinushan Mapatuna Wed, 12 May 2004 08:19:36 GMThttp://www.sqlmag.com/article/sql-server/trading-stocks#commentsAnchorhttp://www.sqlmag.com/article/sql-server/trimming-blanks#commentsAnchorFri, 09 Apr 2004 10:56:06 GMT
Cant you at design time make a field property to not allow spaces? There is such a thing]]>semiprecious.com Fri, 09 Apr 2004 10:56:06 GMThttp://www.sqlmag.com/article/sql-server/trimming-blanks#commentsAnchorhttp://www.sqlmag.com/article/reporting2/reporting-sales#commentsAnchorFri, 12 Mar 2004 05:12:30 GMT
select userid, ordernum, shipdate, orderdate from order_details o where orderdate in (select top 2 orderdate from order_details where userid = o.userid order by orderdate desc)]]>Valery Tesher Fri, 12 Mar 2004 05:12:30 GMThttp://www.sqlmag.com/article/reporting2/reporting-sales#commentsAnchorhttp://www.sqlmag.com/article/sql-server/inserting-order-details#commentsAnchorFri, 05 Mar 2004 15:00:24 GMT
The solution provided below is not complete. It does not validate the XML. I developed an extended stored procedure which performs validation of XML against XDR schema. Why isn’t it published here? Ihor Bobak.]]>Ihor Bobak Fri, 05 Mar 2004 15:00:24 GMThttp://www.sqlmag.com/article/sql-server/inserting-order-details#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorFri, 28 Mar 2003 18:49:47 GMT
This was exactly what I needed. Fantastic tip! Your site will be my number one online reference tool!]]>HarveyFri, 28 Mar 2003 18:49:47 GMThttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorhttp://www.sqlmag.com/article/quering/recent-orders#commentsAnchorThu, 13 Mar 2003 17:35:11 GMT
Both solutions are wrong. (first solution) one will bring more than two orders if there are 3+ orders in the same day. /* 6 */ WHERE o1.orderdate IN ( SELECT TOP 2 o2.orderdate /* 7 */ FROM order_details AS o2 /* 8 */ WHERE o1.userid = o2.userid /* 9 */ ORDER BY o2.orderdate DESC ) (second solution) second one will not be a recent order if there are two more orders in the last orderday. (for example: ’7/5/01’ one order, ’7/7/01’ two order it will return ’7/7/01’ ,’7/5/01’) /* 18 */ SELECT MAX( o3.orderdate ) /* 19 */ FROM order_details AS o3 /* 20 */ WHERE o3.userid = o1.userid /* 21 */ AND o3.orderdate david wei Thu, 13 Mar 2003 17:35:11 GMThttp://www.sqlmag.com/article/quering/recent-orders#commentsAnchorhttp://www.sqlmag.com/article/activex/creating-indexed-views#commentsAnchorFri, 21 Feb 2003 14:33:36 GMT
I ran into the same problem but solved it differently. I implemented an indexed view to speed up a DTS package that was taking too long and got a dramatic performance improvemnt. However other packages were failing with the error message you described. Solution: create/drop the view and its clustered index dynamically. I added 2 steps to my package, 1 step to create view and index at the beginning of the package, another step to drop the view at the end of the package, and everything returned to normal... voila!!!!! Rachid]]>Rachid Ould-Hamouda Fri, 21 Feb 2003 14:33:36 GMThttp://www.sqlmag.com/article/activex/creating-indexed-views#commentsAnchorhttp://www.sqlmag.com/article/tsql3/corrupted-characters#commentsAnchorThu, 09 Jan 2003 12:15:58 GMT
Is the answer supposed to be a single query or can it be a SQL Batch?]]>Jared Birdsall Thu, 09 Jan 2003 12:15:58 GMThttp://www.sqlmag.com/article/tsql3/corrupted-characters#commentsAnchorhttp://www.sqlmag.com/article/stored-procedures/collation-conflict#commentsAnchorFri, 15 Nov 2002 04:18:08 GMT
The problem with collation conflicts when using temporary tables is driving us nuts. We did think of the first solution presented here, setting the colation on the temp table explicitly, but it does not work not in installations that mix collations and - surprise?! - regional settings on server and client (Win2K as well as WinXP). We’re still looking for a solution that will work for any collation on any SQL Server installation with any regional setting on the server with any regional setting on the client. Any ideas?]]>Adriaan Simons Fri, 15 Nov 2002 04:18:08 GMThttp://www.sqlmag.com/article/stored-procedures/collation-conflict#commentsAnchorhttp://www.sqlmag.com/article/stored-procedures/synchronizing-logins#commentsAnchorThu, 12 Sep 2002 06:43:30 GMT
i needed this badly, thank you.]]>Jay S Thu, 12 Sep 2002 06:43:30 GMThttp://www.sqlmag.com/article/stored-procedures/synchronizing-logins#commentsAnchorhttp://www.sqlmag.com/article/quering/returning-the-rows#commentsAnchorFri, 12 Apr 2002 12:12:33 GMT
In my opinion the solution picked for this problem is not that great. The problem states that given a set of values for c, the solution picked assumes you know how many values there are in the set or that the values for the given set are in a table, rather that just a set like (’Value #1’, ’Value #2’). Just an opinion.]]>DanFri, 12 Apr 2002 12:12:33 GMThttp://www.sqlmag.com/article/quering/returning-the-rows#commentsAnchorhttp://www.sqlmag.com/article/quering/returning-the-rows#commentsAnchorThu, 11 Apr 2002 02:57:28 GMT
Nice technique. But generally speaking, this technique is valid only if you’re searching on two fields that make up a unique key. Otherwise you must match the row count for all i values with the row count for i values that have one of the requested values for c, like: SELECT Base.i FROM (SELECT i, COUNT(*) AS Result FROM #Test GROUP BY i HAVING COUNT(*) = 2) AS Base INNER JOIN (SELECT i, COUNT(*) AS Result FROM #Test WHERE c=@Value1 OR c=@Value1 GROUP BY i) AS Compare ON Base.i = Compare.i AND Base.Result = Compare.Result]]>Adriaan Simons Thu, 11 Apr 2002 02:57:28 GMThttp://www.sqlmag.com/article/quering/returning-the-rows#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorWed, 16 Jan 2002 14:09:23 GMT
I think there’s a cleaner and faster way to accomplish this task as follow. First find out what column(s) are the unique key from the table has duplicate records. Second perform the following query: select distinct col1 --assume this is the unique column, col2, col3 into test2 from test Finally, truncate the test table and insert the record back into test.]]>Chor Kwan Wed, 16 Jan 2002 14:09:23 GMThttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorThu, 10 Jan 2002 16:52:24 GMT
Your solution will also delete those records in the table that do not have a duplicate. In this case, it works because all rows in the table have duplicate. If you have a row that does not have duplicate, it will be deleted by your delete statement as well.]]>Quyen Nguyen Thu, 10 Jan 2002 16:52:24 GMThttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorThu, 10 Jan 2002 16:02:48 GMT
This will give a better result CREATE TABLE test (uid_test INT IDENTITY, str_f_name VARCHAR(20), str_l_name VARCHAR(20), str_other_data VARCHAR(20)) Go INSERT INTO test (str_f_name, str_l_name, str_other_data) VALUES (’Bugs’, ’Bunny’, ’Other Data 1’) INSERT INTO test (str_f_name, str_l_name, str_other_data) VALUES (’Bugs’, ’Bunny’, ’Other Data 2’) INSERT INTO test (str_f_name, str_l_name, str_other_data) VALUES (’Fred’, ’Flintstone’, ’Other Data 1’) INSERT INTO test (str_f_name, str_l_name, str_other_data) VALUES (’Fred’, ’Flintstone’, ’Other Data 2’) INSERT INTO test (str_f_name, str_l_name, str_other_data) VALUES (’Tom’, ’Jerry’, ’Other Data 1’) INSERT INTO test (str_f_name, str_l_name, str_other_data) VALUES (’Bob’, ’Taylor’, ’Other Data 1’) Go SELECT * FROM test Go SELECT uid_test INTO #b FROM test WHERE (uid_test NOT IN (SELECT MAX(uid_test) FROM test AS a GROUP BY str_f_name, str_l_name HAVING COUNT(*) > 1 ) OR uid_test IN (SELECT MIN(uid_test) FROM test AS a GROUP BY str_f_name, str_l_name HAVING COUNT(*) = 1 )) Go DELETE FROM test WHERE uid_test NOT IN (SELECT uid_test FROM #b) Go SELECT * FROM test Go DROP TABLE test DROP TABLE #b Go]]>Bob Taylor Thu, 10 Jan 2002 16:02:48 GMThttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorThu, 10 Jan 2002 15:36:17 GMT
This will also delete rows that were not duplicated it does not do what I believe you intended.]]>Bob Taylor Thu, 10 Jan 2002 15:36:17 GMThttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorWed, 09 Jan 2002 17:52:20 GMT
Regarding InstantDoc #23703 (dumping the dulicates): Try this script. Same results, but more efficent. use pubs go SELECT IDENTITY( int ) AS IDCol,a1.au_id, a1.au_fname, a1.au_lname, a1.phone, a1.address, a1.city, a1.state, a1.zip INTO #a1 FROM pubs..authors AS a1 CROSS JOIN pubs..authors AS a2 CROSS JOIN pubs..authors AS a3 CREATE CLUSTERED INDEX IX_a_name1 ON #a1 ( au_lname, au_fname ) ALTER TABLE #a1 ADD CONSTRAINT PK_a_IDCol1 PRIMARY KEY NONCLUSTERED ( IDCol ) go delete from #a1 where #a1.IdCol > (select min(b.idcol) from #a1 b where b.au_lname = #a1.au_lname and b.au_fname = #a1.au_fname)]]>Jerry Berlin Wed, 09 Jan 2002 17:52:20 GMThttp://www.sqlmag.com/article/sql-server-2000/dumping-the-duplicates#commentsAnchorhttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorThu, 06 Dec 2001 11:40:23 GMT
How do I find out in SQL 7.0 , which indexes in the database are being used the most , so that I could separate them into a different filegroup for faster I/O]]>Arindam Sen Thu, 06 Dec 2001 11:40:23 GMThttp://www.sqlmag.com/article/quering/querying-the-sysindexes-system-table#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/reading-the-registry#commentsAnchorSun, 17 Jun 2001 19:16:52 GMT
thanks for the good tip! but I meet a problem: if I have a user with no password,how can I use the SP? thank you!]]>zero100 Sun, 17 Jun 2001 19:16:52 GMThttp://www.sqlmag.com/article/sql-server-2000/reading-the-registry#commentsAnchorhttp://www.sqlmag.com/article/sql-server-70/scaling-back-the-schedule#commentsAnchorWed, 30 May 2001 08:24:59 GMT
How do you schedule a differential backup automaticaly? I have not found anything in the maintainance plan. SQL7]]>thad skinner Wed, 30 May 2001 08:24:59 GMThttp://www.sqlmag.com/article/sql-server-70/scaling-back-the-schedule#commentsAnchor