I want to update a table with consecutive numbering starting at 1. The update has a where clause so only results that meet the clause will be renumbered. Can I accomplish this efficiently without using a temp table?
Sample query: Here is and another version where you can the code of the trigger:
Sample 1: Sample with a table: Results (New York Time):
“ I have been asked several times for advice on how to effectively maintain a production database. Sometimes the questions come from none-DBAs who are implementing new solutions and want help fine-tuning maintenance practices to fit their new databases’ characteristics. As with the majority of tasks and procedures in the IT world, there isn’t an easy one-size-fits-all solution for effective database maintenance, but there are some key areas that nearly always need to be addressed. My top five areas of…
So, someone had stored thousands of SSN numbers in a table but now they have an issue because some of them are incomplete or are not correctly formatted. Thank you to blog.sqlauthority.com and little creativity I have come up with a SQL script that fixes this issue. Here is the T/SQL script:
This article explains the base functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL. Samples script: Sample Results: Variable Data Results ——————– HELLO WORLD RIGHT Results ————- RLD LEFT Results ———— HEL CHARINDEX Results —————– 6 SUBSTRING Results —————– LO WO Combined Results ——————– HELLO
The following instructions are supposed to return the same results, but sometimes the results can be different. The reason is that SERVERPROPERTY automatically reports changes in the network name of the computer, while @@SERVERNAME don’t. SELECT CONVERT(sysname, SERVERPROPERTY(N’servername’)) GO SELECT @@SERVERNAME When they are run on the default instance they return just the server name, but when run over a named instance they return the server name in the following format: servername/instancename.
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. Here are some samples:
Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.
