Databases – Page 3 – www.myw0.com

Oracle PL/SQL – SQL Plus Startup Script with glogin.sql or login.sql

02/06/2017

This article shows about how to configure the environment variable when SqlPlus – SqlPlus Command start up in order to have always a good behavior in the formatting of the result. You have to setup the Windows – Environment Variable (SQL Plus|SQL Developer)- SQLPATH with a directory. Copy then the file login.sql described below in it. This file is execute each time that you connect to a database with SQLPlus. SQLPATH in Windows environment (Windows Oracle server): HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ SQLPATH in…

T-SQL Script to Group Data by Minute

01/25/2017

| No Comments

| Microsoft SQL, SQL

If you are doing a mass data insert into a table and you want to know how fast is inserting data. I have created a script that groups data by minute, based in a DateTime data type field.

Converting UTC Time To Local Time in T-SQL – Sample

12/08/2016

| No Comments

| Microsoft SQL, SQL

Sample 1: Sample with a table: Results (New York Time):

T/SQL Splint String/Text

10/26/2016

| No Comments

| Microsoft SQL, SQL, T-SQL Code

One common task performed in database applications is given a full name, how can this be split into the first name and last name. In this article, three different methods are discussed to perform this task using three different functions in SQL Server. It is assumed in this article that the full name is just made up of one first name and one last name. Using SUBSTRING Function The first method is the use of the SUBSTRING string function,…

T/SQL Generate Random Numbers

10/26/2016

| No Comments

| Microsoft SQL, T-SQL Code

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…

T-SQL Comma Delimited Output

10/26/2016

| No Comments

| Microsoft SQL, T-SQL Code

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…

Built-in tools troubleshoot SQL Server memory usage

10/14/2016

| No Comments

| Microsoft SQL

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;…

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

10/14/2016

| No Comments

| MySQL

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…

Create View in MySQL

10/12/2016

| No Comments

| MySQL

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:

Tips for Effective SQL Server Database Maintenance

08/18/2016

| No Comments

| Microsoft SQL, SQL

“ 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…

Category: Databases

Oracle PL/SQL – SQL Plus Startup Script with glogin.sql or login.sql

T-SQL Script to Group Data by Minute

Converting UTC Time To Local Time in T-SQL – Sample

T/SQL Splint String/Text

T/SQL Generate Random Numbers

T-SQL Comma Delimited Output

Built-in tools troubleshoot SQL Server memory usage

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

Create View in MySQL

Tips for Effective SQL Server Database Maintenance

Menu

Contact Info

–