SQL Server has a built-in function that generates a random number, the RAND() mathematical function. The RAND math function returns a random float value from 0 through 1. It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value. To use it, you can simply do a simple SELECT, as follows: SELECT RAND() AS The result generated by this SELECT statement is as follows (note that…
One of the common tasks performed when retrieving data from a SQL Server database is returning the result as a comma-delimited output instead of a result set. This task can be performed by using a CURSOR selecting the column to be concatenated together. Each row returned by the CURSOR is then concatenated together into a variable separating each one by a comma. Here’s how the script will look like using the table in the Northwind database. A sample…
Dynamic management views Dynamic management views, first introduced with SQL Server 2005, provide information about server and database state. These views are useful for monitoring overall SQL Server health, identifying the root cause of SQL Server performance bottlenecks, and tuning SQL Server instance or database performance. The following is a list of dynamic management views available in SQL Server 20008R2, SQL Server 2012 and SQL Server 2014. You can use these views to obtain SQL Server memory usage information;…
Microsoft Management Console (MMC) hosts administrative tools that you can use to administer networks, computers, services, and other system components. Here is a short list. Certificates certmgr.msc Indexing Service ciadv.msc Computer Management compmgmt.msc Device Manager devmgmt.msc Disk Defragmenter dfrg.msc Disk Management diskmgmt.msc Event Viewer eventvwr.msc Shared Folders fsmgmt.msc Group Policy gpedit.msc Local Users and Groups lusrmgr.msc Removable Storage ntmsmgr.msc Removable Storage Operator Requests ntmsoprq.msc Performance perfmon.msc Resultant Set of Policy rsop.msc Local Security Settings secpol.msc Services services.msc Windows Management…
Searching for answers to an identical “Illegal mix of collations” error with conflicts between utf8_unicode_ci and utf8_general_ci. I found that some columns in the database were not specifically collated utf8_unicode_ci. It seems mysql implicitly collated these columns utf8_general_ci. Specifically, running a ‘SHOW CREATE TABLE table1’ query outputted something like the following: | table1 | CREATE TABLE `table1` ( `id` int(11) NOT NULL, `col1` varchar(4) CHARACTER SET utf8 NOT NULL, `col2` int(11) NOT NULL, PRIMARY KEY (`photo_id`,`tag`) ) ENGINE=InnoDB DEFAULT…
To create a new view in MySQL, you use the CREATE VIEW statement. The syntax of creating a view in MySQL is as follows: CREATE Sample:
If you share your portable media with multiple people and multiple OS and devices and from time to time you may end getting this error message when trying to access a folder or file. You cannot access nor your can delete them. Solution (try this): Click on Start –> Run –> Type cmd and press Enter. “Command Prompt” will be opened. Since you are having problem with G & H drivers, enter the below command chkdsk /f g: –>…
“ 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:
