LINKS
LINKS
MOST RECENT
Edit Release Candidate Ready, Refactor Update Underway
More Source Control Improvements and Bug Fixes
IntelliPrompt, Schema Explorer, and More Improvements
Edit Smarter Help, Speed Enhancements and More
Smart Rename Object, Parameter, Column and Remove Parameter Refactors
New Edit 2008.10 features implemented
Edit 2008.10 in initial testing
|
Developer Diary Posts, comments, and articles straight from ApexSQL Developers and Analysts Edit Release Candidate Ready, Refactor Update Underway Here's an update on what we've been doing during the past week and a few more days on top of that: two things actually - getting ApexSQL Edit version 2008.10 ready for production and mass deployment and planning the next ApexSQL Refactor to implement the new Refactors that Edit already received. Finally after many weeks of development and parallel daily testing of ApexSQL Edit 2008.10 Beta builds we managed to reach sufficient quality level and create Edit 2008.10 RC build. To ensure the quality before public release we are now performing final and thorough regression testing on the RC Edit build to make sure nothing's "broken". I would again like to publicly express gratitude and much thanks to all of ApexSQL Edit power-users and all customers who tried out the Beta versions and reported back any issues. Beta reported issues were usually highly prioritized within the development team and quickly resolved so the final Edit 2008.10 version will be much more stable. Since the new Refactors are ready, implemented and working in ApexSQL Edit 2008.10, we had a bit time to improve the user interface and representation of the informative tabs of the Smart Rename Refactors. The below are a few new screenshots of how the improved Smart Rename Refactor UI looks:   We haven't forgotten about the ApexSQL Refactor SSMS add-in - these new Refactors implemented in ApexSQL Edit 2008.10 (Smart Rename Object, Smart Rename Column, Smart Rename Parameter and Remove Parameters) will all be implemented in the immediate next ApexSQL Refactor version 2010.02. The next version will also feature the same major Refactor speed boost that Edit 2008.10 has (up to 600% compared to current versions!) Plans for this next ApexSQL Refactor version also include some of the Refactors UI overhaul towards better usability. These UI improvements will be visible in ApexSQL Edit 2008.11. More on the planned changes and screenshots coming soon. Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit, ApexSQL Refactor ...More Source Control Improvements and Bug Fixes The last week showed ApexSQL Edit 2008.10 is gaining in quality and we have less and less reported QA issues. As we had some more time available from only working on bug-fixes and from targeting performance improvements we also decided to implement one more micro-feature requested for some time now. This is Source Control Explorer individual file Delete functionality. It might be a bit strange that ApexSQL Edit supported deletion of whole Source Control projects (folders / subfolders) but never supported removal of individual files. Now this functionality is also available through the new "Delete" Source Control Explorer file context menu functionality. On the other hand we are still focused on resolving any and all reported priority defects before releasing Edit 2008.10 to production. If you feel there is an issue that has not been addressed for a long time or that was introduced in the recent few Edit 2008 versions, it is still not too late to send a mail to edit_team@apexsql.com and ask for the internal Edit 2008.10 build to see if the issue is now resolved. If the issue persists, let us know and we'll try to prioritize before releasing the next version. Just this week we were able to address and resolve total of 4 old Edit 2008 exceptions. One of them, error when copying a single cell from the results grid when filters row is turned off, existed for several product versions now and much thanks to the user who was patient enough to isolate the issue and report it we have now fixed it in the internal Edit build. In order to better isolate any future issues, we've greatly enhanced ApexSQL Edit logging capabilities. Starting with Edit 2008.10, application will not generate any log files when started normally which will improve the overall performance a bit. However, running the application with the "/log" parameter specified will start generating a very verbose application log file named "ApexSQLEdit.log" in the executable folder. Sending this file to Edit support team together with a bug report will allow us to quickly isolate and fix the issue in question. Our focus is also on the new Refactors. Smart Rename and Remove Parameter Refactors are working well in ApexSQL Edit 2008.10 and we're planning to introduce them soon into our ApexSQL Refactor product version 2010.02 along with a couple of new Refactors currently being readied for production. Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit, ApexSQL Refactor ...IntelliPrompt, Schema Explorer, and More Improvements We're still working hard on delivering ApexSQL Edit version 2008.10 first RC to final QA round and to production. As almost all potential "showstopper" issues are now resolved internally, we had some more time to focus on quality and performance, usability, micro-features and enhancing ApexSQL Edit help documentation. Mentioned in the previous diary post, one of the major improvements coming with Edit 2008.10 is more context sensitive and detailed help documentation. This week we have reintroduced support for offline help documentation, CHM help file. Downloading Edit CHM help file and placing it in the application installation folder will give the offline help priority over the online help pages. Advantage of this is much faster access to help pages. Note that we will keep updating both online and offline help documentation in parallel. Another micro-feature I promised to mention in the last diary post is a small but very useful IntelliPrompt improvement. For a long time ApexSQL Edit supports "global aliases" feature - assigning custom aliases to objects directly in Schema Explorer or from the Alias Management section of Options. The aliases are automatically appended to the object name after it is inserted using the IntelliPrompt auto-completion feature. And starting from Edit 2008.10, all valid global aliases will be shown in the IntelliPrompt auto-completion lists:  Selecting an alias from the list will auto-complete both the full object name and append the alias. We are planning more such micro-enhancements going forward. Many of them will be mined from the feature requests that were gathered through the Forum or e-mail support straight from Edit users. I'm happy to say we had several positive remarks received directly from ApexSQL Edit users who tried out Beta builds of Edit 2008.10 regarding performance improvements. It is now confirmed that working with the remote SQL Servers and with large queries is now much faster, both in what regards Query Editor, IntelliPrompt and all Edit Refactors. If you are also experiencing performance issues with the currently public Edit version, please send us a mail describing the issues and we will provide the new Edit 2008 Beta build. And speaking of the new Refactors, we have prepared even more NEW Refactors to be implemented both into future ApexSQL Edit versions and ApexSQL Refactor Add-in. More about this will be disclosed soon! Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit, ApexSQL Refactor ...Edit Smarter Help, Speed Enhancements and More The past week has been interesting, mostly for the fact that we are running out of QA critical issues to fix and nearing the ApexSQL Edit 2008.10 RC build. Development team has been focusing more on optimizations and a few more micro-features. There's already a large list of enhancements introduced into ApexSQL Edit 2008.10 (more details on the previous developer diary post). We didn't stop there and implemented some more usability improvements and optimizations. One of the new areas where we are focusing our software analyst forces is ApexSQL Edit's online help documentation. Since several Edit versions ago, ApexSQL migrated all help documentation online instead of an offline CHM help file. This allowed for always up-to-date access to the application help topics. We are now in process of major update of ApexSQL Edit's online help documentation and the new version will soon be uploaded. Our goal for the new help documentation is making sure that ALL Edit features are described in details. If you find any part of Edit help documentation incomplete or not helpful enough, please send us a mail to edit_team@apexsql.com and we'll treat all such missing/incomplete help mails with top priority. Help documentation should also include all previously "hidden" Edit features and describe them in details. Goal of the help is to make sure there are no such things as advanced features and that everyone can learn and use Edit to its full extent. Also related to the help documentation, developer team has greatly enhanced help request context sensitivity. This actually means that using F1 key to invoke help from different areas of Edit will open the most specific help topic for the current application context. I.e. if you are working with Object Explorer, details for Object Explorer help topic will be displayed. Same goes for Schema Explorer, Source Control Explorer, Template Explorer, Results panel, Source Control commands, and much more. Working with the new Refactors introduced in ApexSQL Edit 2008.10, we've identified an opportunity to optimize initialization of both new and the old Refactors in Edit. Following the trail, developer team managed to optimize Refactors usage on both local, broadband and narrowband connections with visible speed improvements of up to 600% when working on remote SQL Servers. This covers Qualify Object Name, Expand Wildcards and Encapsulate As refactors. Note that this Refactor optimization will also be included in the upcoming ApexSQL Refactor version 2010.02. More details about some new ApexSQL Edit 2008.10 functionalities in the next developer diary post... Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...ApexSQL Edit 2008.10 update Here's a quick update on ApexSQL Edit version 2008.10 development and testing progress as we're nearing the end of the development and bug-fixing process. For users who want to know what is new in Edit 2008.10, here's a short update list: 1. Visual Table Editor 2. A couple of new Refactors 3. Large number of known bugs fixes We've implemented one of the long awaited fixes / enhancements: preservation of UI customizations between application version upgrades or re-installs. Please note that this is a new feature of Edit 2008.10 and that previous (currently public) Edit versions don't have it so upgrading to Edit 2008.10 will reset all UI customizations for one last time. We again apologize for this inconvenience. We will make sure that going forward all user customizations, no matter how small, are preserved on upgrades or re-installs. Another mini-feature included with Edit 2008.10 is IntelliPrompt support for WITH common table expressions. IntelliPrompt will now offer auto-completion lists for WITH CTE's when "Show Temporary Table IntelliPrompt" Editor Option is enabled. I can confirm that we've identified a performance slowdown when using Edit over slow remote connections to SQL Servers. This issue is now fixed and using Query Editor should be as fast for remote SQL Server connections as it is for local / LAN connections. Please note that this speed improvement has no effect on slow connections when queries are returning large datasets as data retrieval completely depends on the speed of the connection to SQL Server. And talking about speed improvements, ApexSQL Edit development team's focus has shifted towards performance optimizations a lot. We are working on dedicated application profiling including execution speed, memory consumption and access to SQL Server instances. Areas identified containing performance bottlenecks will be treated as top priority defects and resolved ASAP. Quality is of the utmost importance and ApexSQL took QA testing much more seriously starting with ApexSQL Edit 2008.10. Besides standard internal QA testing, we've prepared hundreds of repeatable regression test cases to execute and confirm none of the existing Edit features is broken during the latest development cycle. I personally feel the improved QA standards are giving good results as our team of power-users / beta testers (who we can't thank enough for continued help with Edit testing) are finding fewer bugs on their own. Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...Smart Rename Object, Parameter, Column and Remove Parameter Refactors ApexSQL Edit 2008.10 beta development and testing is nearing the final stages. In the meantime as promised last week, here are some screenshots of the newly introduced Refactors. First a reminder that we've introduced a total of 4 different new Refactors: Smart Rename Object Refactor, Smart Rename Parameters Refactor, Smart Rename Columns Refactor, and Remove Parameter Refactor All new Refactors are accessible through the Schema Explorer context menu of corresponding objects. Smart Rename Refactors are available for: 1. Tables, Views, Stored Procedures and Functions, 2. Columns of Tables and Views, and 3. Parameters of Stored Procedures and Functions. Remove Parameter Refactor can be invoked from Schema Explorer context menu of Stored Procedure or Function objects. Although the Smart Rename Refactors support generating smart rename scripts for multiple object types, they all share a similar user interface dialog:  Warnings tab will contain warnings in case there are any (i.e. encrypted objects encountered). There were none in the example above. Action in Sequence tab will show the order of operations that will be performed by the generated smart rename SQL script. Referencing Objects tab will point to all of the database objects that will be affected by the generated script - all depending objects. The Remove Parameters Refactor contains a list of all parameters of the selected Stored Procedure or a Function. Remove script will be generated for the selected parameter:  Resulting script for all new Refactors, both Rename and Remove can be appended to the already opened Query Window or opened in a new Query Window. Scripts are always manually executed after that to complete the rename / remove operation. Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...New Edit 2008.10 features implemented We've been promising for some time now to have a couple of enhancements available to ApexSQL Edit, mainly Visual Table Editor functionality. The wait is finally over and we are now in the testing phase of the beta Edit version containing this much requested feature. Some more details about Visual Table Editor with screenshots are available in Previous diary post. The table editor is available using New Table / Edit Table context menu commands in ApexSQL Edit's Schema Explorer of any table object or "Tables" type folder. This new feature effectively eliminates the need of creating or editing tables using SQL scripts or any other external tools. What is one of the highlights of Edit's Table Editor is the smart table editing / differentiating engine providing the safest scripts for modifying existing tables. Here are a couple of screenshots of the new Table Editor directly from ApexSQL Edit 2008.10:    And a couple of new Refactors are now readily available in Edit 2008.10. Announced on previous posts, they are: Smart Rename Object Refactor, Smart Rename Parameters Refactor, Smart Rename Columns Refactor, and Remove Parameter(s) Refactor First three Refactors share the almost identical user interface. I promise the final screenshots to be available next week in the next diary post as we are currently ironing out final remaining issues in the beta ApexSQL Edit build. Our dedicated team of software testers is now confirming quality of ApexSQL Edit existing features, new Visual Table Editor and the new Refactors. A number of power-users are also helping out by regularly reporting any new found issues with Edit beta builds which the development team prioritizes to resolve before making Edit 2008.10 public. We are also thinking about making future Edit beta builds publicly available to all users. This would make some fixes to known issues available earlier and to everyone. It will also make sure more people can try out the new features early and provide feedback while the iron is still hot. Any comments about this idea are welcome. Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...New Visual Table Editor Visual Table Editor allows easy creation and editing of table structure objects from the application without writing code. A table editor contributes to making ApexSQL Edit a one-stop shop for database development. Here is a sneak preview:    Table Editor allows easily manipulation of table columns: add a new or remove an existing column, set or remove a primary key, modify extended properties ...  With property editor you can easily change most of table properties, including table name, schema, description, identity specification. Future versions will include additional functionalities. Stay tuned for more details. AUTHOR Dejan Apostolovic Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...New VIPs selected We sort of jumped the gun on this a bit but we wanted to make selections and get prizes out before year end Here are the new VIPs. Most of these are the same VIPs as from mid 2009 but we did lose a couple and gain a couple. Announcements have gone out to all and we are still processing replies. If you haven't been contacted please email sales@apexsql.com ASAP. We're offering $50 worth of ApexSQL Gear to all awardees. Our top VIP got a significant cash award as well. Thanks to all of our VIPs for their incredible contribution. We look forward to expanding this VIP program in 2010. How do I become a VIP? Easy - post on our forum, report bugs and otherwise accumulate VIP points. If you get enough to be in the top 20 or so, you will be awarded VIP status. posts are worth 1 point. Confirmed bugs worth 10. The best way to get lots of VIPs points is to become an internal/beta tester. Get early access to our tools and first crack at bugs. Let us know at support@apexsql.com if you are interested Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: VIP program ...Edit 2008.10 in initial testing We are making sure this time and going forward that all ApexSQL Edit releases are tested during whole stage of development in parallel. This will ensure quality from the start to the end of the new version development. Currently while we're resolving all reported issues from ApexSQL Edit 2008.09, wrapping up visual Table Editor and the new Refactors (Remove Parameter, Smart Rename Columns, Smart Rename Parameters and Smart Rename Object), Edit power-users are using the first beta versions of Edit 2008.10 in their regular work. Internally in ApexSQL, we've also boosted our QA testing team manpower and initiated repetitive regression testing using our constantly increasing database of test cases. Our focus is on the IntelliPrompt that many users noted needs further improving: speed, responsiveness, functionality and less invasive behavior. All these changes towards improving QA will ensure good quality ApexSQL production releases approved by both internal company testers and our most helpful power-users. I'd like to point out an interesting "feature request" case originated from a very old forum support thread (sometime in 2004). It just shows that persistence pays off if you really need a fix or a change of current Edit behavior. This is something we are increasingly doing in the past few versions - promoting some original "feature requests" into usability defects that would obviously improve the application usability or improve user unfriendly behavior. By getting a feature request "promoted" into a defect means that it will be implemented ASAP, usually in the first or the second upcoming Edit version. But please also note that everything posted as a feature request is never dropped or ignored. It just takes a longer route into being processed and eventually approved for implementation alongside the major features we work on all the time (note the first paragraph above). And as noted in the Edit Paint Job diary post, this new Edit version will have even more new menu command icons implemented as well as a Windows Vista / Windows 7 full quality application icon looking nice on any resolution and icon size setup. Who says the Bitmap Doesn't Make the Application? Thanks, Goran Bogdanovic ApexSQL Edit Development Team Leader Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit ...New Remove Parameter Refactor New Remove Parameter Refactor enables you to remove a parameter for an SQL Server Function or Procedure (Triggers included).  This refactor will be released in ApexSQL Edit 2008.10 and in ApexSQL Refactor 2010. In a case where parameter is dropped for a function, the script generated by this refactoring will: 1) Alter function signature by dropping its input parameter 2) Update all dependent objects that have referenced this function with a new function signature 3) Will NOT update function body. Here is how Alter function signature works. For an example if you have a function You will get this change script to drop parameter @P2 Here is an example how dependent objects will be updated. For an example, if you have 2 functions [MYFUNCTION] and [MYFUNCTION2] and [MYFUNCTION2] references [MYFUNCTION] (see highlighted) After you drop a parameter @P2 for a function [MYFUNCTION] you will get a change scrip that will update [MYFUNCTION2] with a reference to a new [MYFUNCTION] signature (see highlighted). Here is an example to show that function body remains intact. If you drop a parameter @P2 for [DBO].[MYFUNCTION] The change script will not update parameter @P2 from the function body, but will alert a warning: “--Removed parameter is used in object script”. In a case where a parameter is dropped for a procedure, the script generated by this refactoring will: 1) Alter procedure signature to drop a parameter (either input or output) 2) Update all dependent objects that have referenced this procedure with the new reference 3) Will NOT update procedure body Here is an example that illustrates all: Note that if you drop a parameter @P2 for a procedure [DBO].[MYPROCEDURE] you will get this change script -- Warnings: -- Removed parameter is used in object script. Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit, ApexSQL Refactor ...Connection Monitor Manager ApexSQL Log Connection Monitor in current version of ApexSQL Log inserts one new row into msdb.dbo.APEXSQL_LOG_LOGIN database for each new login. Starting ApexSQL Log 2008.05, the status of the Connection Monitor and the APEXSQL_LOG_LOGIN table location will be fully configurable. The Connection Monitor Manager can be launched from the Tools main menu  Connection Monitor Manager dialog consists of the two tabs: a. Connection Status  This shows currently selected server and status of the Connection Monitor on it, which may be Running (Connection Monitor continuously captures data), Not Running (when Connection Monitor is stopped) or Disabled (when Connection Monitor is disabled). Authentication options allows to select account which will run the Connection Monitor on SQL Server start up, whether it will be the account that is running SQL Server (Default authentication) or some Domain account (Windows authentication) or predefined SQL Server account (SQL Server authentication). This is fully configurable on the Connection Status tab. b. Data Capture  This tab consists of two areas for configuration: database where login information is stored and captured information maintenance settings. Using the Default value for the database field, all information is stored into APEXSQLLOG, if it exists. Captured Information Maintenance allows you to determine whether to maintain the information yourself or to have ApexSQL Log maintain the information. In case Automatic maintenance is selected, the number of days to keep information must be specified. By default, this is equal to 60 days. If manual captured information maintenance is preferred "I will maintain the information myself" should be selected. Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Log ...New Smart Rename Columns Refactor New Smart Rename Columns Refactor enables you to rename columns in tables. This refactor takes into account all dependent objects that use columns you selected for renaming and generates the script that renames them in all places (for example, inside procedure bodies, views etc…). Renaming will add a new column to the table. Then all data is copied into this column from the old one and then all dependent objects will be updated with the new column name.  This refactor will be released in ApexSQL Edit 2008.1 and in ApexSQL Refactor 2010. Let's review example of renaming column ErrorID to ErrorLogID for ErrorLog table of AdventureWorks database. Before refactoring CREATE TABLE [dbo].[ErrorLog]( [ErrorID] [int] IDENTITY(1,1) NOT NULL, [ErrorTime] [datetime] NOT NULL, [UserName] [sysname] NOT NULL, [ErrorNumber] [int] NOT NULL, CONSTRAINT [PK_ErrorLog_ErrorID] PRIMARY KEY CLUSTERED ([ErrorID] ASC) ) Column ErrorID is renamed to ErrorLogID after refactoring BEGIN TRAN GO ALTER TABLE [dbo].[ErrorLog_Old] DROP CONSTRAINT [PK_ErrorLog_ErrorID] GO -- Preserving data from [dbo].[ErrorLog] into a temporary table temp1936061983 IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ErrorLog]') AND [type]='U')) EXEC sp_rename @objname = N'[dbo].[ErrorLog]', @newname = N'temp1936061983', @objtype = 'OBJECT' GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ErrorLog] ( [ErrorLogId] [int] IDENTITY(1, 1) NOT NULL, [ErrorTime] [datetime] NOT NULL, [UserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ErrorNumber] [int] NOT NULL, ) GO -- Disabling constraints ALTER TABLE [dbo].[ErrorLog] NOCHECK CONSTRAINT ALL GO -- Restoring data IF OBJECT_ID('[dbo].temp1936061983') IS NOT NULL AND EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ErrorLog]') AND [type]='U') EXEC sp_executesql N' SET IDENTITY_INSERT [dbo].[ErrorLog] ON INSERT INTO [dbo].[ErrorLog] ([ErrorLogId], [ErrorTime], [UserName], [ErrorNumber]) SELECT [ErrorID], [ErrorTime], [UserName], [ErrorNumber]] FROM [dbo].temp1936061983 SET IDENTITY_INSERT [dbo].[ErrorLog] OFF ' GO -- Enabling backward constraints ALTER TABLE [dbo].[ErrorLog] CHECK CONSTRAINT ALL GO ALTER TABLE [dbo].[ErrorLog] ADD CONSTRAINT [PK_ErrorLog_ErrorID] PRIMARY KEY CLUSTERED ([ErrorLogId]) ON [PRIMARY] GO -- Dropping the temporary table temp1936061983 IF OBJECT_ID('[dbo].temp1936061983') IS NOT NULL DROP TABLE [dbo].temp1936061983 GO IF @@TRANCOUNT>0 COMMIT SET NOEXEC OFF Let’s review another example of renaming column PostalCode into PCode which is referenced from the View . Before refactoring CREATE TABLE [Person].[Address]( [AddressID] [int] IDENTITY(1,1) NOT NULL, [City] [nvarchar](30) NOT NULL, [PostalCode] [nvarchar](15) NOT NULL, CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED([AddressID] ASC) ) GO CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_City_PostalCode] ON [Person].[Address] ( [City] ASC, [PostalCode] ASC ) CREATE VIEW [HumanResources].[vEmployee] AS SELECT e.[EmployeeID] ,a.[City] ,sp.[Name] AS [StateProvinceName] ,a.[PostalCode] FROM [HumanResources].[Employee] e INNER JOIN [HumanResources].[EmployeeAddress] ea ON e.[EmployeeID] = ea.[EmployeeID] INNER JOIN [Person].[Address] a ON ea.[AddressID] = a.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] GO After refactoring column PostalCode is renamed to PCode in the Table definition (sp_rename), in the Index (DROP/CREATE INDEX combination) and in the VIEW by its altering: DROP INDEX [IX_Address_City_PostalCode] ON [Person].[Address] GO EXEC sp_rename N'[Person].[Address].[PostalCode]', N'PCode', 'COLUMN' GO CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_City_PostalCode] ON [Person].[Address] ([City], PCode) GO ALTER VIEW [HumanResources].[vEmployee] AS SELECT e.[EmployeeID] ,a.[City] ,sp.[Name] AS [StateProvinceName] ,a.[PCode] FROM [HumanResources].[Employee] e INNER JOIN [HumanResources].[EmployeeAddress] ea ON e.[EmployeeID] = ea.[EmployeeID] INNER JOIN [Person].[Address] a ON ea.[AddressID] = a.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit, ApexSQL Refactor ...New Smart Rename Parameters Refactor New Smart Rename Parameters Refactor allows renaming parameters in procedures and functions. This refactor takes into account all references from another procedures that use parameters which selected for renaming and generates script that renames them in all places (for example, inside procedure bodies etc…). Renaming will add a new parameter to the procedure. All dependent objects will be updated with the new parameter name.  This refactor will be released in ApexSQL Edit 2008.1 and in ApexSQL Refactor 2010. Let's review example of renaming parameter StartProductID to ProductID for uspGetWhereUsedProductID procedure of AdventureWorks database Before refactoring CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID] @StartProductID [int], @CheckDate [datetime] AS BEGIN SET NOCOUNT ON; WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [StandardCost], [ListPrice], [RecursionLevel]) AS ( SELECT b.[ProductAssemblyID], b.[ComponentID], p.[StandardCost], p.[ListPrice], 0 FROM [Production].[BillOfMaterials] b INNER JOIN [Production].[Product] p ON b.[ProductAssemblyID] = p.[ProductID] WHERE b.[ComponentID] = @StartProductID AND @CheckDate >= b.[StartDate] AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) ) SELECT b.[ProductAssemblyID], b.[ComponentID], b.[StandardCost], b.[ListPrice], b.[RecursionLevel] FROM [BOM_cte] b GROUP BY b.[ComponentID], b.[ProductAssemblyID], b.[RecursionLevel], b.[StandardCost], b.[ListPrice] ORDER BY b.[ProductAssemblyID], b.[ComponentID] END; GO After refactoring parameter StartProductID is renamed to ProductID: CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID] @ProductID [int], @CheckDate [datetime] AS BEGIN SET NOCOUNT ON; WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [StandardCost], [ListPrice], [RecursionLevel]) AS ( SELECT b.[ProductAssemblyID], b.[ComponentID], p.[StandardCost], p.[ListPrice], 0 FROM [Production].[BillOfMaterials] b INNER JOIN [Production].[Product] p ON b.[ProductAssemblyID] = p.[ProductID] WHERE b.[ComponentID] = @ProductID AND @CheckDate >= b.[StartDate] AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) ) SELECT b.[ProductAssemblyID], b.[ComponentID], b.[StandardCost], b.[ListPrice], b.[RecursionLevel] FROM [BOM_cte] b GROUP BY b.[ComponentID], b.[ProductAssemblyID], b.[RecursionLevel], b.[StandardCost], b.[ListPrice] ORDER BY b.[ProductAssemblyID], b.[ComponentID] END; GO Let’s review another example of renaming parameter when it is referenced by name from another procedure: CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin] @EmployeeID [int], @Title [nvarchar](50), @HireDate [datetime] WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [HumanResources].[Employee] SET [JobTitle] = @Title ,[HireDate] = @HireDate WHERE [EmployeeID] = @EmployeeID; END TRY END; GO CREATE PROCEDURE [HumanResources].[UpdateTodayLogin] AS DECLARE @HireDate datetime SET @HireDate = CONVERT(datetime, getDate()) -- Parameter is referenced by name from here EXEC [HumanResources].[uspUpdateEmployeeLogin] @EmployeeID = 1, @Title = N'Analyst', @HireDate = @HireDate; GO Existent procedures are altered and parameter name is updated in definition and all references: CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin] @EmployeeID [int], @Title [nvarchar](50), @date [datetime] WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE [HumanResources].[Employee] SET [JobTitle] = @Title ,[HireDate] = @Date WHERE [EmployeeID] = @EmployeeID; END TRY END; GO CREATE PROCEDURE [HumanResources].[UpdateTodayLogin] AS DECLARE @HireDate datetime SET @HireDate = CONVERT(datetime, getDate()) -- Parameter is referenced by name from here EXEC [HumanResources].[uspUpdateEmployeeLogin] @EmployeeID = 1, @Title = N'Analyst', @Date = @HireDate; GO Get your developers the tools they need to do the job fast and right - ApexSQL Developer Studio is the ultimate combat multiplier for SQL Developers. Best of Class tools - one download, install and discounted price. Click Here for more info. Labels: ApexSQL Edit, ApexSQL Refactor ... |
