Experience Sitecore ! | More than 200 articles about the best DXP by Martin Miles

Experience Sitecore !

More than 200 articles about the best DXP by Martin Miles

Routine for renaming a Helix module

That would be a quick one. Once you decide to rename existing module, you should follow this consequence order to avoid potential errors. When modifying a module, the higher layer of the changed module is - the more changes require to commit. There is a general consequence of steps below, some of them may not be necessary for your module, so you may skip them. Anyway, here we go:

  1. In serialization file, rename module name for each of predicate records. However, do not rename serialization config file itself at this stage. Publish that particular config to web root (Alt + ,; ALT + P hotkey combination) in order to overwrite existing config. That will enable change tracking for serialization for new paths already.
  2. Once published, rename corresponding items in Sitecore, as per serialization predicates changed on the previous step. Keep in mind that not all predicates are listed in serialization as they are configured at other places by the wildcard (ie. templates), but you need also to change them as well. As the result, there will be a new folder created under src\{layer}\{module_name}\serialization\ and it will have all root items serialized, per each of predicate.
  3. No go to Unicorn console (/unicorn.aspx) and re-serialize modified project. That will bring the rest of the items serialized.
  4. Now it's time to rename the module in Visual Studio:
    1. Change solution folder
    2. Change project name, right underneath solution folder
    3. Remove project out of solution and move it into parent folder having a new name, then add this project back to the solution as existing.
    4. Change Assembly Name and Default Namespace, also perform changes at AssemblyInfo.cs file.
    5. Iterate through all C# classes and adjust the namespaces to match the one changed at 4.3, (In case you're using ReSharper, it will highlight you invalid namespace, so hit ALT + ENTER in and select "Move to {your_new} namespace"). For T4 code generated models however it would be nice to delete those models and re-create just to ensure code generates correctly.
    6. Now go to App_Config/include folder and rename configs: {module_name}.{layer}.config and {module_name}.{layer}.Serialization.config
    7. Do the same for other configs, ie. App_Config/Environment/{layer} and so on.
    8. Also, in configs, you'll need to adjust namespaces, where {type, assembly} are referenced. That may be for instance a configuration of dependency injection, route definition, or any other references to custom classes.
    9. Go and check your /Views folder in order to adjust references
  5. Next step is to delete old module already existing at web root (if you're deploying on clean - you may omit this step). To get rid of existing items with old name, delete the following:
    1. App_Config\Include\{layer}\ - all configs having old module name as the part of their filename
    2. Within /bin folder, delete compiled module, for example: {Solution_name}.{old_name}.Website.dll
    3. Clean up /Views folder for old references as well.
  6. Now it is a good time to remove old serialization - it is kept under an old project name, actually almost duplicating the same you've produced at step 3 (except location and serialized paths, of course). 
  7. Before publishing changes to web root folder, go to Sitecore and rename strongly type references within the Sitecore content. That's in case you've got any custom functionality stored within items that having references in format "{srong_type, assembly}", so please go to Sitecore and rename them all to new namespaces, as was set in 4.3
  8. Finally, re-build the solution and use gulp script, select: publish-all-{layer} task.
  9. If you're renaming a project layer - there're few more things to be done. You would likely to change hostName attribute of the site definition node. In that situation, adjust IIS binding and create a hosts file record for that hostname (if not yet there).
  10. Post-deploy step: rename other content stored in Sitecore items. As for example, you could have a layout called oldsiteLayout.cshtml. Despite it still functions, it would be great to change its name to newsietLayout.cshtml for consistency. But in that case, you also need to change that name in Layout definition item for that project in Sitecore. Do that for all layouts or other affected items.
  11. Last, but not the least. Perform manual and/or integration tests in order to make sure your solution won't break. Breaking tests are always good as they make you critically review certain pieces of implementation, refactor them, or at least keep tests actual to your solution.

Hope that guide helps.

Glass Mapper with Sitecore 9 and Helix

I was trying to implement T4 CodeGeneration of Glass Mapper for my current Helix on Sitecore 9 project. In the beginning, it all went well, and I did not suspect any of issues. However, the issues still occurred later when trying to navigate to LaunchPad. I have been presented a very confusing error with the description, saying:

The file '/sitecore/shell/client/Speak/Layouts/Layouts/Speak-Layout.cshtml' does not exist.

on the following screen:


After investigating a call stack, I came to the conclusion that it is definitely something with Glass Mapper. After playing a while with my configuration, I have identified exact getModel pipeline processor that triggered that issue:

  <mvc.getModel>
    <processor patch:before="*[@type='Sitecore.Mvc.Pipelines.Response.GetModel.GetFromItem, Sitecore.Mvc']" 
         type="Glass.Mapper.Sc.Pipelines.Response.GetModelFromView, Glass.Mapper.Sc.Mvc"/>
  </mvc.getModel>

Commenting this line returned LaunchPad fully functional, however, obviously, Glass Mapper stopped working with a familiar Sitecore MVC error:

The model item passed into the dictionary is of type 'Sitecore.Mvc.Presentation.RenderingModel', but this dictionary requires a model item of type 'Sitecore.Feature.Navigation.Models.ILink'.

So I started digging around and assumed that maybe I have got not the latest version of Glass, that does not support Sitecore 9. With that in mind, I updated to latest stable version of Glass, which was 4.4.0.199. After upgrading, I tried to run build but it failed. Again, much time spent on investigation, so later found out that latest version of Glass does not have namespace Glass.Mapper.Sc.Configuration.Attributes. Previously that namespace was at Glass.Mapper.Sc.dll but that dll did not present within the latest package.

Suddenly ReSharper came into a play, obligingly offering to reference required library. After ReSharper added that reference - project compiled successfully. I performed a deployment, and ... it broke again, with the same error. What? Why? How? I couldn't understand that...

Furthermore investigations... at some moment, I noticed that ReSharper referenced one of the old Glass.Mapper.Sc.dll from somewhere, so the very first thing I made sure was to provide thorough sanity check and fix across entire solution. After removing all Glass Mapper trails, I've readded latest version of Glass Mapper again, and expectedly it did not find that namespace, mentioned above.

At this time I took dotPeek and carefully went through all the libraries. Just to find out that there is no such namespace, indeed- in all of the Glass libraries.

But wait! What if... What if.. If I try to check that namespace in the latest beta version?

So I picked up the latest available beta version, which was 4.4.1.331 ...

Install-Package Glass.Mapper.Sc -Version 4.4.1.331-beta

... and voila!!! Namespaces were there. So I decided to go ahead with that beta versions, and trick did work!

After couple more sanity clean-up in both my solution and web folder, I finally did some required configuration changes, then install for certain projects in your solution and finally concluded that solution work well for me.

Sitecore gives 60 days of trial for developers

Previously, it was sort of vicious circle for new developers willing to start learning Sitecore. "How can I start working with Sitecore" - one of the most common questions asked at StackOverflow and (mostly non-Sitecore) user groups when enthusiasts wanted to download their developer copy and play with it on his/her local dev. machine. 

The problem came from a situation when one could install Sitecore only with having a license file, that could be taken from a client he's already working for. Surely, in order to obtain such a job, one should already have reasonable skills with Sitecore obtained from somewhere. Also, Sitecore installers are also not in the free access and require at SDN credentials to be downloaded, even with SIM tool.

Now the process becomes simpler, as Sitecore is going to provide trial licenses for the developers. Currently, they run a pilot testing of that program, offering just 100 developers trial license, with a waiting list for those who not quick enough to get into first hundred. Please read more at the official source of the trial program here: Announcing the preview of the upcoming Developer Trial Program

Hope this helps to attract more developers in order to fill the lack of professionals on the market.

10 Useful tricks for Helix development

1. First, is the most important hotkey that published a single file - whether a static file oк Razor view, to the web folder, preventing you from running gulp script. Selecting the desired file, hold ALT and then click ; (semicolon) and p. Your file will be immediately available at web destination. Then most time-taking is Application pool recycle process, running each time you add/remove/modify a config file or DLL, so current approach prevents you from wasting time on that. The result:



2. Next, in order to run Sitecore in a debugger, one usually need to go to "Debug" menu of Visual Studio, then pick up "Attach to process". Then find w3wp.exe from a long list of available processes after selecting "Show processes from all users" checkbox. When you need to do that multiple times in a day (and you do) it becomes an unwanted waste of time. There is a Visual Studio extension called AttachToAllTheThings that adds 3 handy menu items into Tools menu, one of which is "Attach to IIS". So far so good, but it would be even better to assign a hotkey to make attachments quicker, I use a combination of SHIFT + ALT + I:



3. Working with Helix you often need to run gulp tasks, that is executed from Task Runner Explorer. Normally you need, to go to View menu item then Other windows and there find out Task Runner Explorer. In a clean vanilla Visual Studio you may use CTRL + ALT + BackSpace binding. However those using ReSharper may find that combination re-mapped, so I opt-out in favour of neutral SHIFT + ALT + T combination:



4. If you are using ReSharper, you can benefit from its solution-wide code analysis tool. This is a very handy tool that is often overlooked, but it may give you plenty of insights of what's going wrong with your solution and how to fix that. But please be careful as if it is enabled permanently - if affects your performance. Double click a grey circle at the right bottom corner of Visual Studio in order to enable this feature.


After a while, it presents you with a list of issues you need to review (and likely to fix):



5. Also, one more useful trick of ReSharper will be useful for everyone working with abstractions. We got used to navigating to a class by doing CTRL + Left Mouse Click. But when doing the same on an interface, you'll navigate to the interface definition - not something useful when you expected to get some concrete implementation. Do CTRL + ALT + Left Mouse Click instead and you'll navigate to exact implementation if the is one, or will be presented with a list of all concrete classes implementing that abstraction.


6. Helix: An approach(es) for restoring WebRoot to an initial state of vanilla Sitecore installation.


7. Know your tools: The easiest way to add a new project to your Helix solution


8. Use Helix + Glass Mapper + T4 Templates = Code Generation


9. In addition to T4 Templates code generation, you may use young brother - Visual Studio code snippets. Recently Raul Jimenez prompted at https://github.com/rauljmz/Sitecore.VisualStudio.Snippets.


10. ZeroDeploy from Hedgehog, who also made TDS, Razl and Feydra. This is something unbelievable, you can't imagine that! The idea of the project is to get rid of Application Pool recycles upon deployments of code and config files. How could that be at all possible? ZeroDeploy relied on Sitecore Dependency Injection (that's why it only can run on Sitecore 8.2 or later) and instead of /bin folder it stores versioned libraries into Sitecore database, being even visible at the Sitecore tree! To make this magic possible, it is advised to separate most of your high-level abstractions into standalone libraries, that are very rarely change and that referenced from your web project. At the same time most of the concrete implementation, including Controllers, Services, Repositories and libraries down-referenced by them can be dynamically deployed and loaded back without being put to /bin, retaining the functionality. ZeroDeploy also uses Visual Studio extension and NuGet packages for Sitecore. Seriously, the magic! I am now testing a beta version of ZeroDeploy and am going to write a detailed guide on how to enable this on your development environment (your UAT / staging / prod) still remain untouched.

Hope these tips and tricks will improve your productivity!

Helix working on Sitecore 9

I have managed to make Sitecore 9 and Helix working together. Yaaay!

There is a feature/v9 branch of Sitecore Habitat, where habitat guys unhurriedly adjust Habitat to Sitecore 9. I would advise you to take a look at that at least. One thing to mention (as for 01-Dec-2017) - you'd need to reference all the Sitecore 9 dependencies from NuGet manually, please use *.NoReferences version of each library)

Update: I've recorded entire process of installation Helix with Sitecore 9 on a clean developer machine, that already has Visual Studio 2017, SQL Server 2016 and IIS installed. For installation process also required: PowerShell and Chocolatey. Important: all the manipulations are done as Administrator.

Hope one may find useful this 24 minutes video. Thanks for watching!

Introducing Sitecore Telegram



Telegram Messenger has boosted significantly during past years, as the most convenient and functional with UX well thought and incredibly growing base of users. Since recent, Telegram allows creating informational channels, making itself more than a messenger, but a proper publishing platform.

Apart from that, the messenger is perfect for distributing binaries of any format, it also supports any mobile or desktop platform. With such a simplicity of creating the content and delivering it to all subscribers, Telegram became my communicational app number one.

Today I decided to create a Sitecore channel in Telegram, that would be a merely a news channel for the events happening in a Sitecore world. It will also provide the hottest articles created by the community in recent, with a plans to integrate this channel with Sitecore Link project by implementing a search bot, that will return you with most relevant links according to search criteria provided.

So, if you are already using Telegram messenger, just click this link and subscribe! If not - only two more clicks limits you from using the most functional and secure messenger in the world that keeps you be aware of your favourite digital platform - Sitecore.

Stay tuned!

Sitecore Cheat Sheets

While working with Sitecore, you have to keep in mind plenty of things, few of them not easy to remember. To simplify my life as a developer, I have created a compilation of various cheat sheets for various aspects of work with Sitecore. Having that printed on standard A4 papers I found that not quite convenient, and decided to re-create in more convenient format. Then I thought why not to issue that in a portable book format, and share with hundreds of people it may help.



Table of Content:

  • Sitecore Item API
  • ULR parameters
  • Sitecore queries
  • Sitecore PowerShell
  • Content Search API
  • Admin folder pages
  • Security
  • Databases
  • Mongo
  • Configuration
  • Core database
  • Rules engine
  • Helix / Habitat
  • Razor view extensions
  • Glass Mapper
  • Azure
  • Going live
  • ReSharper most used hotkeys
  • IIS and AS.NET
  • Icons in Sitecore
This is bare minimum I have already completed, however, if there are more of interesting topic - please let me know.

    Symposium 2017 and takeaways

    I've been anticipating that event for so long, and finally, I was there! So let's take a look at what's new have been presented.

    Sitecore 9 and xConnect brought a new era of Sitecore development. Starting from principally different installation approach, Sitecore brings multiple changes, the most significant of which are changes developers used to work with xDB (which now moved to SQL Server) and content search (which is now Solr by default). Now instead of calling xDB directly, we will be using xConnect API, which is very well documented, thanks to Martina Welander and her team.

    CRM connectors have been announced - for both Dynamics and Salesforce. 

    Zenith and Horizon - two Sitecore projects currently in development, but both are very promising. It will change the way we work with the platform, but as for now, there is no way too much information about.

    Marketing Automation has been re-worked to the best. Brimit, a Sitecore partner, has arranged a perfect demonstration stand of xConnect and marketing Automation working together on Sitecore 9, identifying contact's (visitor's) parameters from both online and offline channels and assigning them to certain profile pattern card, with sending them personalised email afterwards.

    Sitecore Cortex will be a new machine-learning technology to be used along with Sitecore xDB in order to increase personalisation and data analysis. 

    I spent decent time talking to guys from the team that builds the core of the platform and Express Upgrade Tool. The last one became a mature intelligent tool allowing do controlled upgrades from almost any recent version of Sitecore to version 9, with identifying all the potential issues and configuration breaking changes. I proud that have suggested few valuable ideas for the product about identifying any customises pipeline changes for the instance.

    Technical Preview of JavaScript Services.

    Thursday morning Symposium finished, but not for the 250+ lucky to be announced Sitecore MVPs of 2017. We have had 2 more thrilling days of MVP Summit.

    Almost everything we have been told or presented there has a "non-disclosure" label, so there's not too much to share. Summit was held at much faster pace, comparing to Symposium itself - longer up to 1 hour long deep presentations, with short 5-10 minutes breaks. We were given a great opportunity to challenge new Sitecore 9 training exam, so that whoever passes that test - becomes Sitecore 9 certified already (as I did!). We also took part in Round Table, where various Sitecore teams were presented at round tables, so MVPs were travelling from one interested table to another, raising questions, suggesting ideas and providing feedback.


    Sitecore 9 is out there


    So, finally, what we all have been waiting for, has happened - Sitecore 9 has been announced and released. To be honest, as an MVP, I already have had an access to a beta of the version 9 of Sitecore since late July. but after an official release, we now can publically talk about that.

    So, what has been new:

    Runtime for Sitecore 9 is .NET Framework 4.6.2 and most of Sitecore 9 NuGet libraries are built against that version of the framework.

    The installation process is the first you meet when trying to play with the platform. And it's worth saying that installation process has been totally reworked. So, welcome Sitecore Install Framework - Powershell modules for install and uninstall and a set of JSON config files that store steps for installing Sitecore. Each of these JSON files relies on a part of installation: xConnect, Solr and Sitecore itself. No SIM support (yet) available, unfortunately.

    I mentioned xConnect - that is a new component and probably the largest architectural change for the platform - a framework that allows you to read and write analytics data to xDB and search providers, keeping collections, processing, search (Solr) and reporting behind itself. Previously we have had 4 different APIs used to deal with xDB contact, depending on its state, so that entire method full of if /else clauses looked monstrous. Since now we do not do direct xDB calls and operate using XConnectClient. That allows updating contacts from any channel at any time and automatic indexing of data. Sitecore XP introduces client certificate authentication in order to communicate with xConnect server, that is a Windows service. Also, a good thing is that this framework is well documented.

    SQL Server 2016 becomes the only storage for all Sitecore databases - content data, xDB, marketing, processing, reporting etc. If you are desperate to keep Mongo - nothing to worry about it is still possible to do, or you may store in the cloud with Microsoft Azure SQL or Azure DocumentDB.

    Solr is now search provider by default, and it is also required for installation by Sitecore Installation Framework. Sitecore 9.0 supports version 6.6.2 and sorry not for having version 7.+ of Solr. It is commonly used to install Solr as a windows service, using nssm as the easiest.

    Sitecore Services Client (SSC) has got the ability to create oData REST service and API key management for securing access to APIs, that would be good for interacting with VUE or React JS.

    Those who struggled with Web Forms for Marketers module previously will enjoy new Sitecore Forms, featuring completely redesigned UX with drag and drop, easy styling, built analytics. Also, there is a multipage wizard for creating complex "split" forms. One can even create a form template and instantiate forms from it! Also, there is form and field metrics showing the corresponding performance.

    One more long waited feature is Dynamic Placeholders, now built into the platform, so that it may be called as @Html.Sitecore().DynamicPlaceholder("name"/*, some optional parameters*/). In general purpose, dynamic placeholders allow the use of the same placeholder key multiple times in the same rendering and across multiple renderings in the same placeholder. We have evidenced multiple implementations, so now good to have a universal standard.

    Marketing Automation tool is much better reworked. It provides a new way to create automated online campaigns in Sitecore. With a user-friendly drag-and-drop interface, Marketing Automation provides an extensible set of tools to processes contact enrollment in campaigns and activities. The Marketing Automation Engine is a stand-alone service that is responsible for enrolling contacts in plans and activities

    Express Migration Tool is already familiar to us. Since version 9 that it supports upgrades to 9 from almost any legacy version of Sitecore.

    Welcome, SPEAK 3, built on Angular 4, now using proper architecture. It is now reworked according to best industry standards, pulls data from Sitecore Services Client and unlike previous versions, it is item unaware. That means no more struggle with Rocks and lack of documentation. There is already a demo project you may download and rework.

    What previously caused pain for both developers, administrators, DevOps was way too much complicated configuration. Sitecore is a truly flexible platform, but the flexibility has hidden cost of every flexible component being registered via configuration, boosting compiled config up to hundreds of thousands of lines. That was taken into consideration and we were given configuration Roles and Layers. Now it becomes possible to configure whether a particular setting or config node should be patched for a specific server role using role:require attribute. An instance can be in one of the following roles: ContentDelivery, ContentManagement, Processing, Reporting, Standalone. Apart from roles, there is also a different level of configuration - the also layers: Sitecore, modules, custom - simple, flexible, allows less patching. With layers one can switch off an entire layer, it can be handy for Sitecore support providing a diagnostics, to disable anything apart from Sitecore to make ensure platform itself is working well.

    What is else new regarding Sitecore 9?
    JSS Framework is staging. Also coming soon Sitecore Commerce 9 with a proper inventory and support for federated authentication, SXA and Azure PaaS, CRM connectors for SalesForce and Dynamics for xConnect.

    Few SQL tricks helping to work with Sitecore

    1. Delete all the databases having a specific prefix, in the example below it will drop all the databases starting with habitat.dev.local_ - this may be helpful when Sitecore 9 installation broke half way down and you'd like to return everything to an initial point, also removing (partly) installed databases:

    USE MASTER
    GO
    
    DECLARE @dbnames NVARCHAR(MAX)
    DECLARE @statement NVARCHAR(MAX)
    SET @dbnames = ''
    SET @statement = ''
    SELECT @dbnames = @dbnames + ',[' + name + ']' FROM sys.databases WHERE NAME LIKE 'habitat.dev.local_%'
    IF LEN(@dbnames) = 0
        BEGIN
        PRINT 'no databases to drop'
        END
    ELSE
        BEGIN
        SET @statement = 'drop database ' + SUBSTRING(@dbnames, 2, LEN(@dbnames))
        PRINT @statement
        EXEC sp_executesql @statement
        END
    


    2. Backup all SQL database having some prefix (in this case, habitat.dev.local_) to a folder (like C:\DatabaseBackups\):

    DECLARE @name VARCHAR(50) -- database name  
    DECLARE @path VARCHAR(256) -- path for backup files  
    DECLARE @fileName VARCHAR(256) -- filename for backup  
    DECLARE @fileDate VARCHAR(20) -- used for file name
    DECLARE @scPrefix VARCHAR (20) -- Sitecore database prefix
    
    -- specify database backup directory
    SET @path = 'C:\DatabaseBackups\' 
    
    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
    
    -- specify the Sitecore db prefix
    SET @scPrefix = 'habitat.dev.local_'
    DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE CharIndex(@scPrefix, name) = 1 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

    3. Shrink all databases larger than a certain number of megabytes (stored in @megabytesMoreToShrink variable):

    DECLARE @megabytesMoreToShrink INT SET @megabytesMoreToShrink = 100
    
    DECLARE @name NVARCHAR(MAX) 
    DECLARE @logical_name NVARCHAR(MAX) 
    
    DECLARE db_cursor CURSOR FOR 
    SELECT db.name AS DBName, mf.Name AS Logical_Name
    FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id Where type_desc = 'LOG' AND (size*8)/1024 > @megabytesMoreToShrink
    
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name, @logical_name  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    
                  exec('USE ' + @name + ' ;ALTER DATABASE ' + @name + ' SET RECOVERY SIMPLE;DBCC SHRINKFILE ('''+@logical_name +''', 1);ALTER DATABASE '+@name +' SET RECOVERY FULL')
    
                  FETCH NEXT FROM db_cursor INTO @name, @logical_name  
    END  
    
    CLOSE db_cursor  
    DEALLOCATE db_cursor

    4. Change sa password for SQL instance, you do not need to know the previous password at all, but need to have an administrative access, for sure. Run osql tool, it's path should be added to the environment variable, but if it's not there, please find it here: "%ProgramFiles%\Microsoft SQL Server\130\Tools\Binn\OSQL.exe":

    osql -S SITECOREDEV -E
    1> sp_password NULL, 'SA_PASSWORD','sa'
    2> GO

    5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:

    -- Get item by Sitecore ID
    SELECT TOP 1000 *
      FROM [habitat_Master].[dbo].[Items]
      WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
    
    -- Get item by ID of parent in Sitecore
    SELECT TOP 1000 *
      FROM [habitat_Master].[dbo].[Items]
      WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
    
    -- Enumerate all the versioned fieds for an item in Sitecore by item ID
    SELECT TOP 1000 
      i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
      FROM [habitat_Master].[dbo].[versionedFields] v
      JOIN [habitat_Master].[dbo].Items i 
        ON v.FieldId = i.ID
      WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
      ORDER BY [Name]

    5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:

    -- Get item by Sitecore ID
    SELECT TOP 1000 *
      FROM [habitat_Master].[dbo].[Items]
      WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
    
    -- Get item by ID of parent in Sitecore
    SELECT TOP 1000 *
      FROM [habitat_Master].[dbo].[Items]
      WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
    
    -- Enumerate all the versioned fieds for an item in Sitecore by item ID
    SELECT TOP 1000 
      i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
      FROM [habitat_Master].[dbo].[versionedFields] v
      JOIN [habitat_Master].[dbo].Items i 
        ON v.FieldId = i.ID
      WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
      ORDER BY [Name]

    6. One of the most popular snippets, to restore default admin / b password. Please keep in mind running that against core database:

    -- use core database
    UPDATE dbo.aspnet_Membership
    SET
        Password='qOvF8m8F2IcWMvfOBjJYHmfLABc=',
        PasswordSalt='OM5gu45RQuJ76itRvkSPFw=='
    WHERE
        UserId = (SELECT UserId FROM dbo.aspnet_Users WHERE UserName = 'sitecore\Admin')

    7. Copy users and all their data including passwords from core database of one instance to another:

    -- Copy all non-existing users and their data from OLD core database to NEW core database
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Applications SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Applications 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Applications WHERE [Sitecore.NEW.Core].dbo.aspnet_Applications .ApplicationId = [Sitecore.OLD.Core].dbo.aspnet_Applications.ApplicationId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Membership SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Membership 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Membership WHERE [Sitecore.NEW.Core].dbo.aspnet_Membership .UserId = [Sitecore.OLD.Core].dbo.aspnet_Membership.UserId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Paths SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Paths 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Paths WHERE [Sitecore.NEW.Core].dbo.aspnet_Paths .PathId = [Sitecore.OLD.Core].dbo.aspnet_Paths.PathId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers .PathId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser .PathId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Profile SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Profile 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Profile WHERE [Sitecore.NEW.Core].dbo.aspnet_Profile.UserId = [Sitecore.OLD.Core].dbo.aspnet_Profile.UserId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Roles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Roles 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Roles WHERE [Sitecore.NEW.Core].dbo.aspnet_Roles.RoleName = [Sitecore.OLD.Core].dbo.aspnet_Roles.RoleName)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions WHERE [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions.Feature = [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions.Feature)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Users SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Users 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Users WHERE [Sitecore.NEW.Core].dbo.aspnet_Users.UserName = [Sitecore.OLD.Core].dbo.aspnet_Users.UserName)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles WHERE [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles.UserId = [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles.UserId)
    
    INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events 
        WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events WHERE [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events.EventId = [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events .EventId)

    8. Delete children item of an item with a given ID directly from SQL (beware item cache). Please make sure you execute that in a context of a Sitecore database (ie. call use Sitecore_master before running this):

    DECLARE @parentId AS UNIQUEIDENTIFIER; SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'
    DECLARE @RowsToProcess  INT
    DECLARE @CurrentRow     INT
    DECLARE @SelectCol1     UNIQUEIDENTIFIER
    DECLARE @Items2Delete TABLE (RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),ID UNIQUEIDENTIFIER)
    
    SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'
    
    INSERT INTO @Items2Delete (ID)
    SELECT [Descendant] AS ID
      FROM [dbo].[Descendants]
      WHERE Ancestor = @parentId
    SET @RowsToProcess=@@ROWCOUNT
    PRINT  @RowsToProcess
    
    INSERT INTO @Items2Delete (ID)
    SELECT ID FROM [dbo].[Items] WHERE ParentID = @parentId
    SET @RowsToProcess=@@ROWCOUNT
    PRINT  @RowsToProcess
    
    SET @CurrentRow=0
    WHILE @CurrentRow<@RowsToProcess
    BEGIN
        SET @CurrentRow=@CurrentRow+1
        SELECT
            @SelectCol1=ID
            FROM @Items2Delete
            WHERE RowID=@CurrentRow
    
      DECLARE @id AS UNIQUEIDENTIFIER;
    
      SET @id = @SelectCol1
      PRINT @id
    
      EXEC sp_executesql N'DELETE FROM [Items]
            WHERE [ID] = @itemId
    
            DELETE FROM [SharedFields]
            WHERE [ItemId] = @itemId
    
            DELETE FROM [UnversionedFields]
            WHERE [ItemId] = @itemId
    
            DELETE FROM [VersionedFields]
            WHERE [ItemId] = @itemId',N'@itemId UNIQUEIDENTIFIER',@itemId=@id
    
      EXEC sp_executesql N'DELETE FROM [Descendants] WHERE [Descendant] = @itemId',N'@itemId uniqueidentifier',@itemId=@id
      EXEC sp_executesql N' DELETE FROM [Links] WHERE [SourceItemID] = @itemID AND [SourceDatabase] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
      EXEC sp_executesql N'DELETE FROM [Tasks] WHERE [ItemID] = @itemID AND [Database] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
    END

    Hope that helps you!