[T-SQL] Function for similar phrases |
- Function for similar phrases
- HierarchyID performance problems... Really???
- Column name or number of supplied values?
- Odd behaviour from sys.dependencies
- CTE - Temp table - CROSS APPLY
- CHAR(10) sometimes work
- Diff b/w Temp tables varibles and CTE's
| Posted: 04 Apr 2013 12:26 AM PDT Hi All, Is there any built in function available for checking the similarity of the phraseseg: US Eastern District Court is similar to Eastern District Court of US. So if I am comparing both i should get result 1.I need this for checking the duplicate entries in a table having so much data, so that we can delete the duplicates.Thanks In Advance... |
| HierarchyID performance problems... Really??? Posted: 02 Oct 2011 04:09 AM PDT I heard tales of performance problems associated with the HierarchyID datatype in SQL Server 2008. Since I haven't actually used the HierarchyID datatype (and, therefore, haven't used any of it methods up 'till now), I built myself a nice "little" million row "clean" table in the form of an Adjacency List (including an extra column called "Sales") and converted it to use the HierarchyID data-type. I used the indexes that Microsoft Suggested for such a thing.Then, I built several queries just to try my hand at it (tried them on a smaller 14 node Hierarchy first, just to make sure things worked correctly). All of these queries are based on what I've been able to Google as "hierarchyid performance problems".1. Find all Descendants (all employees in the "down-line" tree) for a given node.2. Find all Ancestors (all managers in the "up-line" chain) for a given node.3. Find all Siblings (all nodes at the same level) for a given node.4. Find the SUM of sales for all Descendants 7 levels "down" for each of the million nodes in the entire hierarchy and insert into a new table.Although I'm sure I could tweak a couple of things here and there insofar as indexing goes, I'm just not seeing what I would call either a performance problem or a resource usage problem.So, my questions are... has anyone actually experienced a performance problem using the HierarchyID data-type and associated methods? If so, could you describe what you were doing and, perhaps, even post some code that demonstrates the problem?Thanks for the help, folks. |
| Column name or number of supplied values? Posted: 03 Apr 2013 11:59 PM PDT Hi,what is wrong in my coding? I want monitor some time interval for IO pending task witing in queue..Error:Msg 213, Level 16, State 1, Line 1Column name or number of supplied values does not match table definition.[code="sql"]create table PendingIO ([database] varchar (100),Physical_name varchar (100),io_pending int,io_pending_ms_ticks int,io_type varchar (20),num_of_reads int,num_of_writes int, time_stamp datetime default getdate())insert into PendingIO values ( '[database]', 'Physical_name', 'io_pending', 'io_pending_ms_ticks', 'io_type', 'num_of_reads', 'num_of_writes')SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs.num_of_reads , fs.num_of_writes FROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_idAND fs.file_id = mf.file_id ORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;[/code]thanksananda |
| Odd behaviour from sys.dependencies Posted: 03 Apr 2013 08:46 PM PDT (SQL Server 2008 R2 SP1)Hi all, I have an SSIS package that dynamically builds a set of SQL statements based on the dependencies in a set of procs. The SSIS package makes use of the dm function [url=http://msdn.microsoft.com/en-us/library/bb677185(v=sql.105).aspx]sys.dm_sql_referenced_entities[/url] to build up a distinct list of columns required from the source system. The code is run against every proc in the database into a working table, then a distinct list is used to pick up data from the source system.We've been testing this for weeks and weeks in UAT, the day we're going live, UAT has fallen over with a really odd issue :crazy:. Basically, very randomly, the values in the ImportColumn are presented with some characters returned with unexpected characters, only ever the first 4 digits, it can be different rows from the resultset and just to make it more complicated how often it returns is completely random. As the SSIS package iterates through each table, I've lifted one of the scripts it builds to explain, this is extracting dependencies from a proc called staging.uspInstrumentHolding.[code="sql"]SELECT DISTINCT Ref.referenced_database_name AS ImportDatabase ,Ref.referenced_schema_name AS ImportViewSchemaName ,SUBSTRING(Ref.referenced_entity_name, (CHARINDEX('_', Ref.referenced_entity_name) + 1), LEN(Ref.referenced_entity_name)) AS TableName ,SUBSTRING(Ref.referenced_entity_name, 1, (CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName ,Ref.referenced_minor_name AS ImportColumnFROM sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding', 'OBJECT') AS RefWHERE 1 = 1 AND Ref.referenced_minor_name IS NOT NULL AND ref.referenced_schema_name = 'import'[/code]The values that return in a strange state are only from the ImportColumn. I can run the script 7-8 times accurately, then the results will appear like this:[code="plain"]UNPOSTEDꀨ⃗가⃗RLꀨ⃗가⃗ANPCCVRT䀨噠RE䀨噠RT䀨噠EF䀨啨UV䀨啨RT䀨䒰TION䀨䒰VT䀨䒰EF䀨䒰LT䀨䎰RL䀨䎰CTION䒰忰AL䀨亰UP䶸忰Fꀨ⃗ꗐ⃗TEꀨ⃗ꗐ⃗Tꓘ⃗뿰⃗TL[/code]Next time I run them, they look like this:[code="plain"]UNPOSTEDUNSERLUNTRANPCCVRTPCDIREPCINRTPCIREFPCORUVPCSRRTPDACTIONPDINVTPDIREFPDMULTPDSERLPDUACTIONPDUVALRPCOUPRPREFRBDATERBRATRBTITL[/code]As you can see, only SOME of the values have returned in this state (and which values are returned in this state changes), it is only ever the first 4 characters and how often it occurs changes.It's just so random, we're yet to identify a trend or reason for this, can anyone offer any suggestions?ThanksJJ |
| CTE - Temp table - CROSS APPLY Posted: 03 Apr 2013 07:52 PM PDT I had some free time at work and was trying out some silly queries when I came across this.CTE referenced in a subquery does not yield any result. I didn't expect this but I can understand why it can happen. Why doesn't the parser throw an error in that case?Here are the queries:[code="sql"]--Subquery with CTE - was surprised by the output;WITH cteProcDependenciesAS(SELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]FROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAME)SELECT pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]FROM cteProcDependencies pdGROUP BY pd.ROUTINE_NAME--Subquery With Temp tableIF OBJECT_ID('tempdb..#cteProcDependencies') IS NOT NULLDROP TABLE #cteProcDependenciesGOSELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name] INTO #cteProcDependenciesFROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAMESELECT pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM #cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]FROM #cteProcDependencies pdGROUP BY pd.ROUTINE_NAME--With cross apply, as my teammate suggested;WITH cteProcDependenciesAS(SELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]FROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAME)SELECT pd.ROUTINE_NAME, tab.c [enc_ROUTINE_NAME]FROM cteProcDependencies pdCROSS APPLY (SELECT enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME) [Tab] (c)[/code] |
| Posted: 03 Apr 2013 03:49 AM PDT IN SQLI want to print for example[quote]Ali is going to Alex[/quote]so I said[quote]declare @x varchar(max) set @x = 'Ali' + CHAR(10) + 'is going to Alex'[/quote]in some servers it work very well in tohers not even with using CHAR(13)the output appear in one line instead of two ones any idea how to fix that, and make it work in all servers |
| Diff b/w Temp tables varibles and CTE's Posted: 02 Apr 2013 02:39 AM PDT Hi guysi have a doubt abut temp table and CT E's which one is faster and why,in which sceniro we will go for this one can u briefly explain this topics with simple examplesAnd can u briefly explain the Derived tables |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment