Archive

Archive for the ‘SQL’ Category

Store and Reading connection string in web.config

August 14, 2015 Leave a comment

Handy Tips for freshers:
Connection string in .NET 3.5 (and above) config file:
Do not use appsettings in web.config. Instead use the connectionStrings section in web.config.

<add name="myConnectionString" connectionString="server=\SQLEXPRESS;database=myDb;uid=myUser;password=myPass;" />

To read/access the connection string into your code, use the ConfigurationManager class.
string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

Always store the connection string in a config file.
I saw many freshers save connection string on page(Hard Coding) or in session, Never be among them.

Connection string in .NET 2.0 config file
In the appSettings location, add a key named whatever you like to reference your connection string to.


To read the connection string from code, use the ConfigurationSettings class.
string connStr = ConfigurationSettings.AppSettings("myConnectionString");

Hope this will help few!

Ref: https://www.connectionstrings.com/store-connection-string-in-webconfig/

Advertisements

sql server 2008 – Create Schema -In just 3 steps

October 13, 2014 Leave a comment

How to create schema sql server 2008:
1. Right Click on DB in which you need to create new schema.
Then Select New Schema.

sql server 2008 - create schema

sql server 2008 – create schema

Remember: Schema is just logical concept and used for db permission and provide easy maintainability.

2. In the Schema – New dialog box, on the General page, enter a name for the new schema in the Schema name box.

3. In the Schema owner box, enter the name of a database user or role to own the schema. Alternately, click Search to open the Search Roles and Users dialog box.
In my case, its dbo.

Get Schema owner

Get Schema owner

Categories: ASP.net C#, SQL

How to find a text inside SQL Server procedures / triggers?

October 8, 2014 Leave a comment

Hi,
Today I come across an change request at my recent project:
Request was to replace a table reference to new view created in all Procedures and functions.

At first glace, I just annoyed by the change, then mu colleague has suggested a way to search though all Procedures Views and functions,

I got details from Stackoverflow.com

Code for your reference:

DECLARE @Search varchar(255)
SET @Search='TheTableNameYouareSearching'

SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1

You will get all listing.Really great…Saved lot of time.

Sorting result explicitely in MySQL

January 6, 2010 1 comment

Hello,
I you want to sort the result explicity & specify the order of which ‘order by’ comes back in,
like if you had a priority field that had the values “Low” “High” or “Medium” .. do this:

select * from tablename order by priority=’High’ DESC, priority=’Medium’ DESC, priority=’Low” DESC;

Another Smart way is :

SELECT * FROM tickets ORDER BY FIELD(priority, ‘High’, ‘Normal’, ‘Low’, ‘The Abyss’);

I observed that its not necessary to mention all the possible values.

e.g. The query we built was quit complex
SELECT b.mf_cocode,b.mf_schcode,b.mf_schname,b.grpcode,b.moptionname,b.mplanname
FROM
(SELECT c.mf_cocode,c.mf_schcode,c.mf_schname,c.grpcode,c.moptionname,c.mplanname
FROM mutual_category c
ORDER BY c.moptionname=’growth’ DESC,c.mplanname=’regular’ DESC) b
GROUP BY b.grpcode

Categories: database, mysql, SQL, tricks

MySQL Query Optimization – Indexing

September 30, 2009 Leave a comment

Indexing is the most important tool you have for speeding up queries. Other techniques are available to you, too, but generally the one thing that makes the most difference is the proper use of indexes.

MySQL uses indexes in several ways:

* As just described, indexes are used to speed up searches for rows matching terms of a WHERE clause or rows that match rows in other tables when performing joins.
* For queries that use the MIN() or MAX() functions, the smallest or largest value in an indexed column can be found quickly without examining every row.
* MySQL can often use indexes to perform sorting and grouping operations quickly for ORDER BY and GROUP BY clauses.
* Sometimes MySQL can use an index to reading all the information required for a query. Suppose that you’re selecting values from an indexed numeric column in a MyISAM table, and you’re not selecting other columns from the table. In this case, when MySQL reads an index value from the index file, it obtains the same value that it would get by reading the data file. There’s no reason to read values twice, so the data file need not even be consulted.

Ref:- http://www.informit.com/articles/article.aspx?p=377652

Categories: database, mysql, PHP, SQL

Mysql Query Cache – Current Cache Size

September 28, 2009 Leave a comment

HI Buddies,
I leaned something new about Mysql Query caching.
Indeed it would be good to be able to see query cache contents.
Unfortunately it is not possible in current MySQL version. So you’ve just got to guess.

SESSION shows the values for the current connection.
mysql> SHOW GLOBAL STATUS;
+———————————–+————+
| Variable_name | Value |
+———————————–+————+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |

| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 2 |

| Threads_created | 217 |
| Threads_running | 88 |
| Uptime | 1389872 |
+———————————–+————+

#Qcache_free_memory –
The amount of free memory for the query cache.
#Qcache_hits –
The number of query cache hits.
#Qcache_inserts –
The number of queries added to the query cache.
#Qcache_queries_in_cache –
The number of queries registered in the query cache.

For Details Ref :- http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Qcache_free_memory

Byte to MB relation (16777216 bytes
16384.000 kilobytes (abbreviated as KB or Kb*)
16.0000 megabytes (abbreviated as M or MB))

To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache.
When you set query_cache_size to a nonzero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures.
mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1282
Message: Query cache failed to set size 39936;
new query cache size is 0

mysql> SET GLOBAL query_cache_size = 41984;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| query_cache_size | 41984 |
+——————+——-+

For the query cache to actually be able to hold any query results, its size must be set larger:

mysql> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+——–+
| Variable_name | Value |
+——————+——–+
| query_cache_size | 999424 |
+——————+——–+
1 row in set (0.00 sec)

The query_cache_size value is aligned to the nearest 1024 byte block. The value reported may therefore be different from the value that you assign.

To control the maximum size of individual query results that can be cached, set the query_cache_limit system variable. The default value is 1MB.

Setting the GLOBAL query_cache_type value determines query cache behavior for all clients that connect after the change is made. Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this:

mysql> SET SESSION query_cache_type = OFF;

If you set query_cache_type at server startup (rather than at runtime with a SET statement), only the numeric values are allowed.

query_cache_type = 2; means You can cache query on defmand using
SELECT SQL_CACHE id, name FROM customer;
SQL_CACHE

The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND.
vice versa,
SELECT SQL_NO_CACHE id, name FROM customer;

The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases.
For details Ref : http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

To check whether the query cache is present in your MySQL server, use the following statement:

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

mysql> SHOW STATUS LIKE ‘Qcache%’;
+————————-+——–+
| Variable_name | Value |
+————————-+——–+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+————————-+——–+

The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache
The optimal value of Qcache_lowmem_prunes is 0.
For details Ref : http://dev.mysql.com/doc/refman/5.0/en/query-cache-status-and-maintenance.html

Categories: database, mysql, SQL, tricks

Join in SQLite

September 14, 2009 Leave a comment

Hi frds,
I need to perform JOIN in SQLite , that time i come to know ,
RIGHT OUTER JOIN and FULL OUTER JOIN is not implemented.

SQL Features That SQLite Does Not Implement:
FOREIGN KEY constraints FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers. The SQLite source tree contains source code and documentation for a C program that will read an SQLite database, analyze the foreign key constraints, and generate appropriate triggers automatically.
Complete trigger support There is some support for triggers but it is not complete. Missing subfeatures include FOR EACH STATEMENT triggers (currently all triggers must be FOR EACH ROW), INSTEAD OF triggers on tables (currently INSTEAD OF triggers are only allowed on views), and recursive triggers – triggers that trigger themselves.
Complete ALTER TABLE support Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
RIGHT and FULL OUTER JOIN LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
Writing to VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
GRANT and REVOKE Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

Ref: http://www.sqlite.org/omitted.html
http://www.geocities.com/colinpriley/sql/sqlitepg06.htm

Categories: database, mysql, PHP, SQL