How to turn off autocommit in phpMyAdmin I want to practice rolling back commands, savepoints and more on phpMyAdmin but I couldn't find a way to turn off autocommit in phpMyadmin. Currently I use the command SET autocommit = 0;.
                                                                                I want to know how, instead of typing this command before each query, if there is another way to perform this task?

You can do this in 3 different ways:

The best answer to this question depends on the context of your request.

If you are doing this from time to time on a dev/test machine (and I certainly hope you're not doing it on an important/production one!), you can then afford to make global changes and you could:

Edit the database interface file inside the "libraries" folder as described here and put the line SET AUTOCOMMIT = 1; in the postConnect function. This will affect every connection made from that instance of phpMyAdmin - you may or may not want this, depending on whether you also use that machine's phpMyAdmin for running stuff on prod?
Put the line autocommit = 0 to set the variable in your my.cnf/my.ini. This now means that every connection to that server will automatically commit (described here). Again, you may or may not want to do this.
Finally, if this is something that you'll want to do from time to time - i.e. experiment at lunchtime/whatever, you can issue a BEGIN [WORK] before you issue your SQL statements! (ibid - aliases of START TRANSACTION - see here).
This has the advantage of not affecting the configuration of either phyMyAdmin or the MySQL server, BUT has the disadvantage of the dev (i.e. you) having to remember to put it in each time. NOT advisable on a production machine, but means that you can use your db tool on both work systems and your experimental ones without worry!

The choice is yours!


Installation of SQL Server 2014 fails on Windows Server 2012 R2 I am attempting to run a silent installation of SQL Server 2014 with SP1 on Windows Server 2012 R2 core install, although it also fails when using UIMODE="EnableUIOnServerCore":

  (01) 2016-02-05 19:48:33 Slp: Running Action: ExpandInstallFeatures
  (01) 2016-02-05 19:48:33 Slp: Error: Action   > "Microsoft.SqlServer.Configuration.SetupExtension.ExpandInstallFeatures" threw an exception during execution.
  (01) 2016-02-05 19:48:33 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Unable to load DLL 'sqlconf.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E) ---> System.DllNotFoundException: Unable to load DLL 'sqlconf.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

  Error result: -2146233052
  Result facility code: 19
  Result error code: 5412

Any thoughts on why sqlconf.dll would not be present? Prior to the installation I have run SqlSupport.msi.

This server is part of a two-node FCI which is meant for AlwaysOn.

Summary.txt is available at https://onedrive.live.com/redir?resid=CBCE97C71A32BCAE%21368391.

Uninstalling SqlSupport.msi allowed the installation to proceed correctly.


Optimal query pattern to count related entries in PostgreSQL Assume I have the following table schema and data

create table categories(
    id integer primary key,
    parent_id integer
);

insert into categories
values
    (1,0),
    (2,0),
    (3,1),
    (4,1);

I need to count the number of children for each entry, i.e. the result should be

 id | parent_id | count
----+-----------+-------
  1 |         0 |     2
  2 |         0 |     0
  3 |         1 |     0
  4 |         1 |     0

The following query produces the correct result but I'm worried about the performance. I guess that it loops through the table as many time as there are rows in it.

select *, (select count(*) from categories b where a.id=b.parent_id) from categories a

It is possible to optimize it?

nested selects  can be slower or be faster depends on how many records are being processed.

select *, (select count(*) from categories b where a.id=b.parent_id)
   from categories a

This can be faster or have the same speed if only a few records or with where clause filters this down

select *, (select count(*) from categories b where a.id=b.parent_id)
   from categories a  where a.id = 5

A faster solution for all the records would be something like this

Select categories.*, coalesce(counted, 0) from categories
   left join ( select parent_id, count(*) as counted from categories group by parent_id) child
   on categories.id = child.parent_id

with this query  planner has an easier time figuring out what to do.  Nested selects the planner has harder time with as it has to treat them as VOLATILE.  meaning it has to keep calling the nested query for each record


Does a mirroring configuration requires the database do stop receiving requests? I want to configure a SQL Server 2012 Mirroring. I know that I have to create a full backup of the Principal server and restore it at the Mirror server. After that, I will backup the transaction log of the Principal and restore it at the Mirror server.

My doubt is: if my Principal database is online (in production) and receiving many insert/delete/update requests, is it a problem? The mirroring and sync can be done without the need of stop my Web application and the Principal Database will be 100% available during this process? I'm worried because the transaction log that will be restore at the Mirror will not represent all transactions because some of then will happen between the backup-restore and the start of the mirroring sync.

After you take your transaction log backup and restore it on the mirrored pair, you might have taken more transaction log backups.  If so, try to catch up on the backups then init the mirror.  At that point all the old transactions that are not replayed with be queued up and you will endure a DB_MIRRORING event on your log file which will stop the transaction log from truncating those transactions that have not replayed yet.  Do note if your network is really slow those transactions might continue to get queued up, it's critical to have an appropiate IO subsystem and network on the 2nd pair so it can keep up.

I'm not sure if you can in your environment, but if the transaction log backups happen too fast, perhaps then you have a IO subsystem problem you need to review on the 2nd pair or a networking problem.  Worst case, you could pause transaction log backups if it isn't going to cause a bad growth issue.  Otherwise you could look at using a DIFF backup to fully catch up then do 1 transaction log restore.

Also, consider high performance mode over high safety mode if you can, but if you can't just make sure your network and diskIO are production quality.  Also set up monitoring for log delays, it will cause massive growth on the primary if you don't.


No records in Mysql.func table even though there are stored functions in server When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . Can someone explain me why its not getting logged . it should create row like mysql.proc table right whenever we create any stored procedure. I am using mysql 5.5.40. Thanks in advance.

All stored procedures and stored functions, whose code is written in the MySQL Stored Procedure Language, reside in mysql.proc.

You can see how many of each with this

SELECT COUNT(1) rowcount,type
FROM mysql.proc GROUP BY type;

The only time I have ever seen mysql.func is in context with AGGREGATE functions:

According to MySQL Documentation on CREATE FUNCTION Syntax for User-Defined Functions

  An AGGREGATE function works exactly like a native MySQL aggregate (summary) function such as SUM or COUNT(). For AGGREGATE to work, your mysql.func table must contain a type column. If your mysql.func table does not have this column, you should run the mysql_upgrade program to create it (see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”).

My guess is that mysql.func stores AGGREGATE functions and not regular functions. Evidently, you would create some UDF in C/C++ and register it in mysql.func using the CREATE AGGREGATE FUNCTION syntax.


When should tables NOT be compressed We have two servers. One we just compressed, but I took over this little project and this guy says some tables are not good candidates. As with many others, performance is -critical-.
Is it accurate to say that some tables shouldn't be compressed? If so, what is a general threshold to look for?
I ran his script to check for each objects reads/writes, but since this looks like a history, not what's occurred during a time frame, this might not be an accurate way to gauge that.

You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.

The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your questions, is goes over the performance implications found when using data compression. My advice is going to be to test it. That is really the only way you will find out for sure if it will benefit or hurt your application/system.

A snippet from the whitepaper points out some data that does not benefit from data compression:

Columns with numeric or fixed-length character data types where most
values require all the bytes allocated for the specific data type
Not much repeating data
Repeating data with non-repeating prefixes
Data stored out of the row
FILESTREAM data


Plan cache behaviour Can someone please explain me why the following is no getting cached

use AdventureWorks2014
go
DECLARE @id INT=43865
SELECT
sp.[BusinessEntityID]
, so.salesorderid
FROM sales.salesorderheader so JOIN sales.salesperson sp
ON sp.[BusinessEntityID] = so.salespersonid
WHERE so.salesorderid = @id

Every time I execute the above query with different parameter, it create an entry in plan cache

by the way, optimize for ad hoc workloads is set

Thanks

You're using a local variable, not a parameter. So because this is part of your query text:

DECLARE @id INT=43865

It gets included as part of the query hash (which converts the query text, including 43865, to binary), and thus produces a different hash every time you change the text. This is how it will work in a Management Studio query window, but it might be different if you send properly parameterized ad hoc queries from a C# application for example.

You can try changing the database setting Parameterization to forced, but this is kind of like killing a mosquito with a bulldozer. Better yet (IMHO), instead of ad hoc SQL, use stored procedures where you pass the value of your local variable @id via a proper parameter (or skip the local variable altogether and just pass 43865 directly to the stored procedure parameter).

Kendra Little has written a little bit about this too, and she suggests using #temporary procedures to mimic your actual procedure (e.g. for testing changes without changing the base procedure). But that is only relevant if you already have a stored procedure in the first place:

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)


How to import blanks as nulls instead of zeros while importing txt using wizard I'm using the Import Wizard to load a text file and need blanks in integer fields to be nulls, but only zeros are inserted.

How to import it properly?

Unfortunately that's not possible if you insist on using the wizard, you would need to edit the package using Visual Studio.

There is an option 'keep null's' when editing the package in Visual Studio

Given this .csv file:

StringField,IntField
a,1
b,5
c,
d,6
e,
f,8
g,
h,
i,10
j,

and this table definition

CREATE TABLE [dbo].[NullTest](
    [StringField] [varchar](50) NULL,
    [IntField] [int] NULL
) ON [PRIMARY]

GO

When you import the data using the wizard the nulls are converted to 0's

However, if at the end of the wizard you select "save package as" instead of "run immediately" as in this screen

The file can be saved somewhere on the file system.

If you then create a new Integration Services project, you can add an existing package like this

If you browse to the file you just saved and view it you will see one data flow task (you will see an execute SQL task too if you selected to create the table instead of appending to an existing one)

Double click the data flow to edit it, and double click the data source to check the "retain nulls" option

Then double click the destination to view the properties, then change the "Keep nulls" property to true.

If you then click the green arrow in the toolbar or select debug > start debugging from the menu to execute your package the end result is this

In the end the wizard is only a wizard. It generates an SSIS package for you and chooses some defaults, unfortunately not the defaults you want.
If you want to keep nulls there isn't really any other way than editing the package with Visual Studio.

Then again, it might be easier to just create the package yourself in Visual Studio or use @MaxVernon's T-SQL solution.


Why isn't to_char IMMUTABLE, and how can I work around it? How can I index a to_char() of a column?

I have tried:

adam_db=> CREATE INDEX updates_hourly_idx
          ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00'));

But got the error:

  ERROR:  functions in index expression must be marked IMMUTABLE

Which seems strange, since the to_char() of a timestamp is reasonably immutable.

Any ideas how to generate that index?

The formats accepted by to_char(timestamp, text) include localized patterns that make it not immutable.

Example of different results with the same input:

test=> BEGIN;
test=> set lc_time='en_US.utf8';
test=> select to_char(now()::timestamp, 'TMDay');
 to_char
---------
 Monday

test=> set lc_time TO 'fr_FR.utf8';
test=> select to_char(now()::timestamp, 'TMDay');
 to_char
---------
 Lundi

test=> END;

If not using this kind of format, the solution is to create your own immutable wrapper function,

CREATE FUNCTION custom_to_char(timestamp) RETURNS text AS
$$ select to_char($1, 'YYYY-MM-DD HH24:00'); $$
LANGUAGE sql immutable;

and then create the index on that function.


What is a LSN (log sequence number)? How does xtrabackup Incremental Backup work? I was surprised that xtrabackup supports incremental backups. xtrabackup copies the data files and understand the format of data files (can have a look inside)? What is a LSN for dummies? I found What is log sequence number ? How it is used in MySQL?.

Quote from Incremental Backup

  An incremental backup copies each page whose LSN is newer than the
  previous incremental or full backup’s LSN. There are two algorithms in
  use to find the set of such pages to be copied. The first one,
  available with all the server types and versions, is to check the page
  LSN directly by reading all the data pages. The second one, available
  with Percona Server, is to enable the changed page tracking feature on
  the server, which will note the pages as they are being changed. This
  information will be then written out in a compact separate so-called
  bitmap file. The xtrabackup binary will use that file to read only the
  data pages it needs for the incremental backup, potentially saving
  many read requests. The latter algorithm is enabled by default if the
  xtrabackup binary finds the bitmap file. It is possible to specify
  xtrabackup --incremental-force-scan to read all the pages even if the
  bitmap data is available.

I still don't understand how this works.
Do you use xtrabackup Incremental Backup? Do you recommend xtrabackup Incremental Backup?

LSN a.k.a. Log Sequence Number is a 8-bytes integer that acts as a version to REDO log entries(ib_logfile*). Every change to data makes an entry in the REDO log and gets a new LSN. The LSN is incremented with every change.

Every InnoDB index page (where user data are stored) has the LSN field in the header. It's an LSN that last modified the page.

So. If we know what the last LSN (let's call it lsn_full) was when we took a full backup, we know if a particular page changed since then. Because if the page's LSN (let's call it lsn_current) higher than lsn_full, the page has changed since the last backup.

Xtrabackup uses this idea to implement incremental backups. As a matter of fact you can implement both incremental and differential backups with Xtrabackup. Check out options --incremental-lsn and --incremental-basedir.

Whether use incremental backups or not depends on how confident you're about the whole process. It's easy to mess up if you don't fully understand what's going on during the backup and restoration process. (At TwinDB I have to deal with lots of cases where incremental backups were not usable on whatever reason).

I highly recommend verifying backups. The most reliable way to ensure a backup copy is usable is to restore the database from it. So I would suggest algorithm:

grab the latest copy.
restore a start MySQL instance on it.
Check some table if it contains recent data. For example, if you store transactions in the database make sure there are some not older than a day or something.


Do I really need to keep .LDF files? Each month we make an end of month snapshot of our production database. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each of these snapshots has an .MDF and .LDF file.

I want to delete the .LDF files and free up some space on the server. Are there any reasons why I need to keep the .LDF files?

Clarification:

Our production database is recreated each night from file extracts off of another system. We only report off of the production database... no updates are ever done.

Nightly Process:
From what I can tell...
Each night the databse tables are truncated
The tables are populated through a series of bulk insert statements
Indexes are rebuilt

You shouldn't delete the log file.  If you are trying to reattach a data file without the log, SQL Server can technically recreate it, but there are a few potential issues, like if there were open transactions when the database was detached.  In which case, you'd have total data loss.

Consume the space, and don't delete your log files.  You're asking for trouble with that.

See this article on Transaction Logs, in particular the "Log Mismanagement" portion.


Database Shrink, Backup, then Restore I understand all the reason why it's not advisable to shrink
I only preface this to avoid falling into that conversation.. so, I have 1.4tb database.. about 700gb is free. This is due to the fact that people have been really irresponsibly making duplicate copies of the data and never cleaning up. In other words, this is space that should be reclaimed.
There are cases when I need to restore 3-4 copies of this database on the same server due to many concurrent development effort that's taking place. In this case, that 700gb waste is multiplied 3x-4x. So, I've been restoring the database to a DBA server then shrinking then backing up the file to be distributed to the lower environment. However, once that 700gb database is restored, it balloons backup to 1.4tb. I thought maybe the "initial size" of the files is doing this but when the backup is taken, the initial file size is small.
As a workaround, we are dropping the files and consolidating. There are 7 data files and we're dropping 2 of them. I would rather avoid this and try to figure out why the data files are ballooning.

The restored database will have the same file size as it had when the backup was produced.
I sometimes call the files "containers". Same number of containers, and same size. They don't have to be at the same location, though (the MOVE option of the RESTORE command).
I don't know what "initial" you refer to above, but whatever it is, it is probably misleading you - and it doesn't change the facts I mention above. :-)
So if you backup a database which has a 100 GB file, that file will be 100 GB when you restore form that backup. If you see something else, then you are probably in a parallel universe. ;-) OR you need to be extremely specific so we follow you to 100% of what is happening. One thing that can be useful here is RESTORE HEADERONLY and RESTORE FILELISTONLY. The later will tell you what size the files will be when you restore.


mysql - order by first condition in where clause I have a table consists of the following fields

id, name

with the following values

1, ciro
2, test ciro
3, ciprox
4, other
5, other

i would like to get all the values that begin with "ci" or contain "ci" but show me the results before they start with ci and then the rest.

the query

select * FROM table WHERE name like 'ci%' or name like '%ci%' order by name;

I returns

1, ciro
2, test ciro
3, ciprox

I want

1, ciro
3, ciprox
2, test ciro

therefore before the rows in which name begins with "ci" and then those that contain

is there a way without using two queries to obtains the result?

This is quite a common problem! A case expression helps here. A query something like (untested)

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

should do what you want.


MySQL: filter date column using LIKE or MONTH? I have a CRON task that needs to extract customers with birthdays in a given month, from a MySQL InnoDB table. The birthday field is indexed and of type DATE.

Filtering for April, I can query the customers table either by:

SELECT *
FROM customers
WHERE birthday LIKE "2015/04/%";

or:

SELECT *
FROM customers
WHERE MONTH(birthday) = 4;

Which one would you recommend, and why?

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type.

Using MONTH(birthday) allows MySQL to grab the month portion of the birthday column that it knows adheres to the DATE data format. When you use LIKE, it's doing character-by-character pattern matching which is significantly costlier and slower to do.

If LIKE was sufficient, then MONTH() wouldn't exist. Any built in function is always going to be the choice over LIKE for a DATE query.


Order by starting from "the middle" in a windowing function What I have is a table like this, where I need to do a running total of the values column for the upcoming months. The issue is that I would like to start from month 4 (shown in column 3) and go all the way around.
What does work is separately creating a new column with the desired ordering and using that to order by, like so:
IIF(IR.[End Month Index] - ID.[Latest Month Index] - 1 < 0, IR.[End Month Index] - ID.[Latest Month Index] + 12 , IR.[End Month Index] - ID.[Latest Month Index]) AS [Upcoming Month IDX]

But it's quite ugly as I have to hard-code the number of the max element (12) and doesn't really feel like a proper solution. Is there a more elegant way to resolve this?

I wouldn't worry about hardcoding the number of months in a year
create table #t (mm int)

insert #t (mm) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

select mm, (mm + 8) % 12 from #t order by 2 asc


PostgreSQL bytea vs smallint[] I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically a few seconds each. Each epoch's record holds the signals for each channel of data as sequential arrays of short integers.

I'm mandated to store the files within the database, in the worst case as BLOBs. Given that, I'd like to investigate options that would allow me to do something more with the data within the database, such as facilitating queries based upon the signal data.

My initial plan is to store the data as one row per epoch record. What I'm trying to weigh up is whether to store the actual signal data as bytea or smallint[] (or even smallint[][]) types. Could anyone recommend one over the other? I'm interested in storage and access costs. Usage is likely to be insert once, read occasionally, update never. If one were more easily wrapped up as a custom type such that I could add functions for analysing of comparing records then so much the better.

No doubt I'm low on detail, so feel free to add comments on what you'd like me to clarify.

In the absence of any answers I've explored the issue further myself.

It looks like user-defined functions can handle all base types, including bytea and smallint[], so this doesn't affect the choice of representation much.

I tried out several different representations on a PostgreSQL 9.4 server running locally on a Windows 7 laptop with a vanilla configuration. The relations to store that actual signal data were as follows.

Large Object for entire file

CREATE TABLE BlobFile (
    eeg_id INTEGER PRIMARY KEY,
    eeg_oid OID NOT NULL
);

SMALLINT array per channel

CREATE TABLE EpochChannelArray (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    channel INT,
    signal SMALLINT[] NOT NULL,
    PRIMARY KEY (eeg_id, epoch, channel)
);

BYTEA per channel in each epoch

CREATE TABLE EpochChannelBytea (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    channel INT,
    signal BYTEA NOT NULL,
    PRIMARY KEY (eeg_id, epoch, channel)
);

SMALLINT 2D array per epoch

CREATE TABLE EpochArray (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    signals SMALLINT[][] NOT NULL,
    PRIMARY KEY (eeg_id, epoch)
);

BYTEA array per epoch

CREATE TABLE EpochBytea (
    eeg_id INT NOT NULL,
    epoch INT NOT NULL,
    signals BYTEA NOT NULL,
    PRIMARY KEY (eeg_id, epoch)
);

I then imported a selection of EDF files into each of these relations via Java JDBC and compared the growth in database size after each upload.

The files were:

File A: 2706 epochs of 16 channels, each channel 1024 samples (16385
samples per epoch), 85 MB
File B: 11897 epochs of 18 channels, each channel 1024 samples (18432 samples per epoch), 418 MB
File C: 11746 epochs of 20 channels, each channel 64 to 1024 samples (17088 samples per epoch), 382 MB

In terms of storage cost, here's the size occupied in MB for each case:

Relative to the original file size, Large Objects were about 30-35% larger. By contrast, storing each epoch as either a BYTEA or SMALLINT[][] was less than 10% larger. Storing each channel as a separate tuple give a 40% increase, as either BYTEA or SMALLINT[], so not much worse than storing as a large object.

One thing I hadn't initially appreciated is that "Multidimensional arrays must have matching extents for each dimension" in PostgreSQL. This means that the SMALLINT[][] representation only works when all channels in an epoch have the same number of samples. Hence File C fails to work with the EpochArray relation.

In terms as access costs, I haven't played around with this, but at least in terms of inserting the data initially the fastest representation was EpochBytea and BlobFile, with EpochChannelArray the slowest, taking about 3 times as long as the first two.


MySQL my.cnf Optimization We are an email marketing company and we recently switched our setup to MySQL. We need to configure MySQL (my.cnf) for extreme performance.

We have tried to configure my.cnf but heavy queries got stuck for many hours without any result. Some queries take one or two hours to complete on our SQL server (8GB RAM).

It's a dedicated MySQL server.

OS = Ubuntu
Installed RAM = 512GB
Table type = MyISAM

We have very large tables (a few tables having more then 70 million rows). Almost every time new tables to match so indexes won't help that much, although the necessary indexes exist.

We do not have any application to connect our database, just 2 users who write adhoc    queries directly.

Below are some variables we have changed from the default values.

key_buffer_size         = 250G
read_buffer_size        = 2G
read_rnd_buffer_size    = 50M
join_buffer_size        = 25M
tmp_table_size          = 5G
sort_buffer_size        = 2G
innodb_buffer_pool_size = 2G
query_cache_limit       = 10M
query_cache_size        = 100M
max_connections         = 20

Below is the MySQLTuner recommendation:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1909G (Tables: 940)
[--] Data in InnoDB tables: 2G (Tables: 3)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 59s (9 q [0.021 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 252.1G global + 4.1G per thread (20 max threads)
[OK] Maximum possible memory usage: 333.6G (66% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 5% (1/20)
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 1% (18/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 10M, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G)

Any help would be greatly appreciated.

Look at this line
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G

The sum of all your MyISAM indexes is 131.6G
You should lower key_buffer_size to 140G. This will give back 110G.
I have a little suggestion for you. You may want to consider using a dedicate keycache for each large table. See my Nov 16, 2012 post Cache all mysql table
Look at this line
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

innodb_buffer_pool_size could be raised to 2560M (That's 2.5G)
Look at these lines
read_buffer_size=2G
sort_buffer_size = 2G

Why is so big? If you change both of these to 32M, you can increase max_connections. In fact, do the following right now:
SET @ThrityTwoMeg = 1024 * 1024 * 32
SET GLOBAL read_buffer_size = @ThrityTwoMeg;
SET GLOBAL sort_buffer_size = @ThrityTwoMeg;
SET GLOBAL join_buffer_size = @ThrityTwoMeg;

Then, go rerun mysqltuner.pl and you will see a dramatic difference from 4.1G per thread
BTW go change join_buffer_size to 32M in my.cnf
Give it a Try !!!
UPDATE 2013-04-11 16:53 EDT
I just thought of another change you can make to increase SELECT speed against MyISAM tables. For every MyISAM you need to read a lot from, do this to the table:
ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

Believe it or not, this will bloat the table 80-100% in size, but SELECTs will go 20-25% faster. I have written about this many times. Here is just few of my posts:

Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?


What is the impact of large varchar data types on indexing? We have some tables in our database that have a data type of varchar(8000) where the longest actual value in that column is 75.  When we place an index on the column we get the expected warning that the maximum permissible index length is 900 bytes.

Does the actual data type impact the effectiveness of this index?  That is, since the actual data is far below 900 bytes, will the index still work?

For what it's worth, I am trying to get the data type modified as this is a ridiculous limit for the column's purpose, but I'm not sure how vehemently to argue.

The very first thing to ensure is what the requirements for the size of the data is.  If it will ever be above 900 then you can't create this index since an insert of more than 900 characters will fail.

Here's an example.

As far as size of the index goes, no, the size of the varchar data type won't affect how big the index is.  Because the data type is varchar, it will only take up the size of the actual data.  If the data changes a lot, though, you will get fragmentations depending on your index fill factor because the allocation for the data will have to grow if more characters are added.


What is the algorithm behind the EXCEPT operator? What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?

David Lozinksi ran a study, SQL: Fastest way to insert new records where one doesn’t already exist He showed Except statement is the fastest for large number rows; closely tying to our results below.

Assumption: I would think Left join would be fastest, as it only compares 1 column, Except would take the longest, since it has to compare All the columns.
With these results, now our thinking is Except automatically and internally takes a hash of each row? I looked at Except execution plan and it does utilize some hash.

Background:
Our team was comparing two heap tables. Table A Rows not in Table B, were inserted into Table B.

The heap tables (from legacy text filesystem) do not have primary keys/guids/identifiers. Some of the tables had duplicate rows, so we found the Hash of each row, and removed duplicates, and created Primary key identifiers.

1) First we ran an except statement, excluding (hash column)

select * from TableA
Except
Select * from TableB,

2) Then we ran a left join compare between the two tables on the HashRowId

select *
FROM dbo.TableA A
left join dbo.TableB B
    on A.RowHash =  B.RowHash
where B.Hash is null

surprisingly the Except Statement Insert was the fastest.

Results actually map close to testing results from David Lozinksi

What is the internal algorithm of how the Except operator works under the covers in SQL Server?

I wouldn't say that there's a special internal algorithm for EXCEPT. For A EXCEPT B, the engine takes distinct (if needed) tuples from A and subtracts rows that match in B. There are no special query plan operators. The distinct and the subtraction are implemented through the typical operators that you would see with a sort or with a join. Nested loop join, merge join, and hash join are all supported. To show this, I'll throw 15 million rows into a pair of heaps:

DROP TABLE IF EXISTS dbo.TABLE_1;

CREATE TABLE dbo.TABLE_1 (
    COL1 BIGINT NULL,
    COL2 BIGINT NULL
);

INSERT INTO dbo.TABLE_1 WITH (TABLOCK)
SELECT TOP (15000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS dbo.TABLE_2;

CREATE TABLE dbo.TABLE_2 (
    COL1 BIGINT NULL,
    COL2 BIGINT NULL
);

INSERT INTO dbo.TABLE_2 WITH (TABLOCK)
SELECT TOP (15000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

The optimizer makes it usual cost-based decisions about how to implement the sort and the join. With two heaps I get a hash join as expected. You can see other join types naturally by adding indexes or by changing the data in either table. Below I force merge and loop joins with hints just for illustrative purposes:

  Does it internally take a hash of each row and compare?

No. It is implemented as any other join. One difference is that NULLs are treated as equal. This is a special type of comparison which you can see in the execution plan: <Compare CompareOp="IS">. However, you can get that same plan with T-SQL that does not include the EXCEPT keyword. For example, the following has the exact same query plan as the EXCEPT query that uses a hash join:

SELECT t1.*
FROM
(
    SELECT DISTINCT COL1, COL2
    FROM dbo.TABLE_1
) t1
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.TABLE_2 t2
    WHERE (t1.COL1 = t2.COL1 OR (t1.COL1 IS NULL AND t2.COL1 IS NULL))
    AND (t1.COL2 = t2.COL2 OR (t1.COL2 IS NULL AND t2.COL2 IS NULL))
);

Diffing the XML of the execution plans only reveals superficial differences around aliases and things like that. The probe residuals for the hash joins do the row comparison. They are the same for both queries:

If you still have doubts, I ran PerfView with the highest available sample rate to get call stacks for the query with EXCEPT and the query without it. Here are the results side by side:

There is no real difference. The call stacks there reference hashing are present because of the hash matches in the plan. If I add indexes to get a natural merge join, you won't see any references to hashing in the call stacks:

Any hashing that occurs is due to the implementation of hash match operators. There isn't anything special about EXCEPT which leads to a special, internal hashing comparison.


Can Set-DbaAgReplica filter any SqlInstances out I have been working with an Availability Group with an extra asyncronous secondary, that is always set to manual failover.

I am worried that if I ran:

Get-DbaAgReplica -SqlInstance x -AvailablityGroup y | Set-DbaAgReplica -FailOver Automatic

it would then set all the sqlinstances to auto failover, I was wondering if you can ignore an instance?

The command works at the Availability Group level - not database level.   Therefore ALL databases that have been added to that Availability Group will failover at the same time.  You cannot have some of them on the Primary and some of them on the Secondary.

If you need to have different databases failover at different times then put them in different Availability Groups.

So NO, it doesn't set all the databases to auto-failover, it sets the Availability Group to auto-failover.


Capacity planning for SQL Server consolidation My company has a small SQL environment for LANDesk and they want to migrate all the servers to a single SQL cluster per region. What I want to know is, how do I determine the capacity for such a scenario? I have obtained the database growth per individual server (All Landesk) and can I take this data as a rough estimate for the consolidation in to the single SQL cluster? The Windows team tells me that the data from these servers is kinda wonky and might not give an accurate result what they want.

The new cluster in question already has a couple of databases but it is not at it's fullest capacity yet. Once they start adding the remaining data from the servers, it should reach it's full capacity. I have been running Perfmon on this for a while and for checking database growth  I used the below TSQL code from sqlskills.com

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

Is there anything I can do to get an accurate result? The LANDesk team doesn't want me to use the older servers and the only option I have is to use the new cluster where they plan to migrate all the data and I only have 6 months worth of data on this. Should I ask them questions like number of years this new cluster be used? etc...

Thank you all for your answers. Here is what I have from the PAL tool

I used the PAL tool to determine perfmon data and these are some of the warnings it gave
  1)Response time greater than 25ms
  2) Less than 10% Idle Time - the disk queue has at least 1 outstanding I/O 90% of the time
  3)Greater than or equal to 64 KB IO sizes. Generally, the larger the IO size, the more data can be transferred per second, but the response times are longer.

4)Physical disk read latency - greater than 25ms
  5)Process % Privileged Time - SQL server using more than 30% of privilged mode CPU usage
  6)SQL Server Access methods FreeSpace Scans/sec - A ratio of more than 1 freespace scan for every 10 batch requests - confirmed with High paglatches, async i/o completion and low page life expectancy
  7)SQLServer:Access Methods Workfiles Created/sec - A ratio of more than 1 workfile created for every 20 batch requests
  8)SQL Buffer cache hit ratio : Less than 97 percent buffer cache hit ratio
  9)Red: Greater than 20 Lazy Writes per second
  10)Red: Page life expectancy is less than 5 minutes (300 seconds)
  11)Yellow : A ratio of more than 1 SQL Re-Compilation for every 10 SQL Compilations

Regarding I/O we are moving to SQL 2012 cluster so I am not sure which is another possible way to measure IOPS. Would you suggest SQLsim or IOmeter etc? Thanks again!

Like Randolph mentioned, disk is the kind of the last thing you need to worry about. If it's clustered as you mentioned, you can easily add storage to accommodate data growth.

The things that are harder to add and cost real money are:

CPUs (Licensing)
Memory (If you're on standard edition, you're limited to 128 GB)

In a consolidation scenario, you need to get a baseline of CPU use (easy perfmon--SQL Server CPU) and memory--harder (you'll want to track to page life expectancy as it relates to total memory use by SQL). Finally, you'll want to tack IOPS--the performance component of storage--if your source environments are on SQL 2014 or higher, there is a counter you can check (disk read/write IO/sec) to get that data. The disk data will simply be cumulative, whilst the CPU and memory data will need to evaluated. In a consolidated environment, you'll probably use a little less CPU and memory.

Finally, for a shared environment like this, you probably want to use Enterprise Edition with Resource Governor so you can prevent the "noisy neighbor" problem.


joining tables without duplicate rows / NULL values so I have multiple tables which I have to merge when query but both join and union doesn't seems to achieve what my object is

Union:

mysql>
SELECT  NAME, currentstatus, NULL AS username, NULL AS status
    FROM  statustable
    WHERE  Name like '%ahsan%'
UNION  ALL
SELECT  NULL AS NAME, NULL AS currentstatus, username, status
    from  awsaccount
    WHERE  username like '%ahsan%';
+--------------+-------------------+----------+--------------------------------+
| NAME         | currentstatus     | status   | username                         |
+--------------+-------------------+----------+--------------------------------+
| Ahsan Naseem | activated         | NULL     | NULL                           |
| NULL         | NULL              | ACTIVE   | ahsan.naseem       |
| NULL         | NULL              | INACTIVE | ahsantest                      |
| NULL         | NULL              | ACTIVE   | ahsantest2                     |
| NULL         | NULL              | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL              | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL              | INACTIVE | decent.ahsan         |
| NULL         | NULL              | INACTIVE | decent.ahsan_cli               |
+--------------+-------------------+----------+--------------------------------+
8 rows in set (0.00 sec)

Join:

mysql>
select  *
    from ( SELECT * from  statustable where     Name like '%ahsan%' ) as ab
    JOIN ( SELECT * from  awsaccount  where username like '%ahsan%' ) as asb;

    +--------------+-------------------+--------------------------------+----------+
    | Name         | currentstatus | username                       | status   |
    +--------------+-------------------+--------------------------------+----------+
    | Ahsan Naseem | activated         | ahsan.naseem                    | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantest                      | INACTIVE |
    | Ahsan Naseem | activated         | ahsantest2                     | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantestuserprofileupdate     | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantestuserprofileupdate_cli | INACTIVE |
    | Ahsan Naseem | activated         | decent.ahsan         | INACTIVE |
    | Ahsan Naseem | activated         | decent.ahsan_cli               | INACTIVE |
    +--------------+-------------------+--------------------------------+----------+
    7 rows in set (0.00 sec)

What I am trying to achieve is :

+--------------+-------------------+----------+--------------------------------+
| NAME         | currentstatus    | status   | username                       |
+--------------+-------------------+----------+--------------------------------+
| Ahsan Naseem | Active            | ACTIVE   | ahsan.naseem        |
| NULL         | NULL              | INACTIVE | ahsantest                      |
| NULL         | NULL              | ACTIVE   | ahsantest2                     |
| NULL         | NULL              | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL              | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL              | INACTIVE | decent.ahsan        |
| NULL         | NULL              | INACTIVE | decent.ahsan_cli               |
+--------------+-------------------+----------+--------------------------------+

I am new to sql and will be grateful for your advice and suggestion

First, I should note that this is basically a formatting requirement: you don't want to repeat the values in the first two columns if they're the same as they were in the previous column. If you have more CPU power available at the application end than on the SQL Server, then you will be likely to get better performance if you can allow the application to handle the formatting requirements.

That said, sometimes the SQL Server has more than enough capacity to handle the formatting, or doing it at the application end doesn't make sense for some reason.

The following query should do what you want:

SET @PrevName := '';
SET @PrevStatus := '';
SET @RowNum := 0;

SELECT Name
      ,currentstatus
      ,`status`
      ,username
  FROM (
        SELECT IF(@PrevName = ab.Name, '', ab.Name) as Name
              ,IF(@PrevName = ab.Name AND @PrevStatus = ab.currentstatus, '', ab.currentstatus) as currentstatus
              ,asb.`status`
              ,asb.username
              ,@PrevName := ab.Name as ActualName
              ,@PrevStatus := ab.currentstatus as Act_curstatus
              ,@RowNum := @RowNum + 1 as RowOrder
          FROM statustable ab
                 CROSS JOIN awsaccount asb
         WHERE (    ab.Name LIKE '%ahsan%'
                AND asb.username LIKE '%ahsan%'
               )
         ORDER BY ActualName, Act_curstatus
       ) sq
 ORDER BY RowOrder
;

I tested this via db-fiddle.com; you can play with the query and test data here.

Quick explanation:

We use the variables to hold the value of Name and currentstatus from the previous row. If Name is different, we display both values; if Name is the same but currentstatus has changed, we leave Name blank but show the new status.

Establishing an order is critically important if we have multiple names or current statuses; if we don't sort these, then rows with different names could be interleaved, meaning the same name would be repeated several times, as seen here:

...
Ahsan Naseem        | activated   | ACTIVE   | ahsan.naseem
                    |             | INACTIVE | ahsantest
Bruce Banner        | activated   | ACTIVE   | mr.green
Ahsan Naseem        | activated   | ACTIVE   | ahsantest2
                    |             | ACTIVE   | ahsantestuserprofileupdate
...

Further, we must preserve the order in which the rows are processed, or else the line with the name might not be the first one, as here:

...
                    |             | INACTIVE | ahsantest
                    |             | ACTIVE   | ahsantest2
Ahsan Naseem        | activated   | ACTIVE   | ahsan.naseem
                    |             | ACTIVE   | ahsantestuserprofileupdate
...

So, we use @RowNum to sequentially number the rows as we process them, and ultimately sort on that number. (I wasn't sure this was needed; however, without it, I got the results back inverted, so I obviously did need it!)

We put our query in a subquery, both so we can force the processed order onto the results, and so we can leave out the Actual_Name, Act_currentstatus, and RowOrder columns.

If you really wanted NULL instead of empty string for the repeated values, just replace the empty string in the IF functions with NULL. As I'm assuming this is for display purposes, empty string is probably a better alternative, but you may have some other reason for doing this.


How do I determine if a table exists in a SQL Server database in SQL Server 2008? How do I determine if a table exists in a SQL Server database in SQL Server 2008?

If you query the sysobjects table, with a query like

SELECT * FROM sysobjects WHERE xtype = 'U' AND name = 'yourTableName'

xtype = 'U' is a user table

you can then wrap this is an IF EXISTS statement

IF EXISTS (SELECT * FROM sysobjects ...)
BEGIN
    ' do your stuff here if it exists
END


SQL Server Max Memory not limiting use of RAM I would like your input on this. I have a sql server 2008r2 Ent. Ed. 64bit with 16 cores and 64GB RAM. There is one instance of SQL server patched fully as of 20111014.

The max ram is set to 60000MB. Amount of free ram is 0 according to task manager after a few days online.

If I change the max ram to below 53GB it will after a few days to stabilize and have some free ram.

It is the sql process that does allocate the ram according to task manager. How do i come to terms with what the problem really is? It goes without saying that i did alot of testing already but havn't solved this to my liking yet. and ohh we do not get the typical memory starvation lagging when the available ram is down to 0 free.

Update 1:

Inspired by another Q/A related to RAM on this page https://dba.stackexchange.com/a/7062/2744 . I used these two to see what the RAM is being used for.

SELECT TOP ( 10 )
        [type] AS [Memory Clerk Type] ,
        SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM    sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC
OPTION  ( RECOMPILE ) ;

SELECT  DB_NAME(database_id) AS [Database Name] ,
        COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM    sys.dm_os_buffer_descriptors
--WHERE   database_id > 4 -- system databases
--        AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC
OPTION  ( RECOMPILE ) ;

The amount used shown by these are
first select 7948432    Kb
second one 44030,57812  MB
that is a total of about 52GB used by sql server... so where did the rest of my RAM go? :-)
Task manager show right now cached 363, available 401, free 40 and sqlservr.exe has Memory private set 64 459 656. Max Ram set to 60000MB as before.

SQL Servers max memory setting defines the limits for buffer pool usage only. There will be variable but significant allocations required over and above that limit.

Jonathan Kehayias's, Christian Bolton and John Samson have level 300/400 posts on the topic. Brent Ozar has an easier to read article that might be a better place to start.

Also related: SQL Server 2008 R2 “Ghost Memory”


In what order does sqllogship.exe process groups of log files? In what order does sqllogship.exe processes groups of log files based solely on filename order or does it does it do it based on file timestamp primary sort then filename secondary sort?

It appears to handles things in filename order when all log files have the same timestamp.

It appears to handles things in filename order

Yes and no. Yes the files are sorted in filename order which generally happens to be the same as the restore order, thus it looks like it goes in order of filename. No, because the files may be tested to see if they contain the log records for the next restore, which means their name could be slightly different and still process. Note that the filename sort is case insensitive and that filenames do matter.


How can you have a combined primary key for one table? I have exactly the same question as posted here.
But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so assuming it can not: what solution would be most recommended?

Original question:

I have a table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. (...)

What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.

This is my current setup:

CREATE TABLE `invoices` (
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoicenumber` mediumint unsigned NOT NULL,
  `branch` enum('A','B') NOT NULL,
  `date` date NOT NULL,
  `client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';

To check the latest invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'

Postgres has sequences, and you can definitely use more than 1 of them for the same table. For example, the following code generates value for invoiceNumber column based on invoiceType in the trigger :

create sequence seq_1;
create sequence seq_2;

create table table1(invoiceType varchar(10) not null,
invoiceNumber int not null,
constraint PK_table1 PRIMARY KEY (invoiceType,invoiceNumber),
constraint CHK_invoiceType CHECK (invoiceType IN ('Type1','Type2')));

CREATE OR REPLACE FUNCTION table1_seq_generator() RETURNS trigger AS $body_start$
    BEGIN
        IF NEW.invoiceType = 'Type1' THEN
          NEW.invoiceNumber := nextval('seq_1');
        ELSIF NEW.invoiceType ='Type2' THEN
          NEW.invoiceNumber := nextval('seq_2');
        END IF;
        RETURN NEW;
    END;
$body_start$ LANGUAGE plpgsql;

CREATE TRIGGER biTable1 BEFORE INSERT ON table1
FOR EACH ROW
WHEN (NEW.invoiceNumber IS NULL)
   EXECUTE PROCEDURE table1_seq_generator();

---------------Testing ----------------------------
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   INSERT INTO table1(invoiceType) values ('Type1');
   INSERT INTO table1(invoiceType) values ('Type2');
   SELECT * FROM table1;

You don't necessary have to do it in trigger, application code itself can get nextval for different sequence.

Also, I'd not use invoiceType,invoiceNumber as primary key constraint, but rather have surrogate pk and unique constraint on  invoiceType,invoiceNumber (or branch,invoiceNumber according to your naming ).


SSMS - How to do case-insensitive searches in Object Explorer Preface: I am not referring to queries.

There are numerous places in SSMS that allow for filtering, such as the Object Explorer and Profiler, but these all treat filters as case-sensitive with no visible option otherwise, so if you search for contains 'ASDF' then values like "ASDF_MyEntity" are included but "asdf_MyEntity" are omitted.

For example, we have a lot of SQL Agent jobs on a large server, and I'm trying to use the Object Explorer to filter them by project name, which we always prefix the job names with. However, these appear as both uppercase and lowercase variants.

Another use case is searching for related stored procedure to a module among thousands of entries. If one is named inconsistently (e.g. PascalCase versus camelCase), the filtered search will omit it. This seems like quite an unnecessary hazard of phantoms for debugging.

Furthermore, the sorting in the Object Explorer places uppercase variants before lowercase (so uppercase Z comes before lowercase a) for every type of entity (e.g. table names, stored procedures, job names, etc.), so I either have to scroll around a lot, or check two different filters (or more if it's not all uppercase or all lowercase letters — 2len(name) times to be precise).

I realize I can just query jobs, tables, and other entities manually, but doing this is nonsensical given that SSMS exists, as its name is "SQL Server Management Studio."

Is there something I can do to make SSMS (and SQL Server Profiler) ignore case like every other Windows application? Perhaps I can change a collation setting locally?

Also, why was this decision made by Microsoft when implementing SSMS? I have found it to be only detrimental.

Furthermore, the loading splash screen for SSMS says "built on Visual Studio" which ignores case in Solution Explorer.

P.S. Using SSMS 2014 (version 12.0.5214.6). I cannot make any server-side changes, and my local development environment should match the target server environment for testing purposes.

It is technically possible to do this case-insensitive filtering, though only due to a bug in SSMS. I don't have SSMS 2014 (version 12.x), but I did get it working in SSMS 17.9.1 and 18.0 Preview 6, connected to an instance with a default collation of Hebrew_100_BIN2.

Attempt Uno (no)

Seeing that the query uses LIKE, I figured I would try using single-character range wildcards (i.e. [ and ]). Passing in a pattern of [Tt][Ee][Ss][Tt] did not work, however, due to certain characters getting escaped. It seems that [, %, and _ all get wrapped in [ and ] to turn them into literals (i.e. no longer having special meaning).

Attempt Dos (Yes)

Seeing that the value used with the LIKE operator (i.e. the value entered into the UI) was concatenated into the query instead of passed in as a variable (i.e. tbl.name like N''%Test%'' instead of tbl.name like N''%'' + @_msparam_XX + N''%''), I started thinking, "Isn't there a reason why it's dangerous to do this with user input? Hmm, I wonder ..." And so I passed in a ' and it was escaped into ''. Normally this is exactly what should happen with a single-quote. However, this is being used in Dynamic SQL, so it's nested one-level deeper than where '' is properly escaped. A string within a string needs a single-quote to be escaped as '''', or double-escaped, because '' merely ends the string within a string.

For example, when filtering on "Tables", the generated code (the one line that matters) is:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Test%'')

If I had passed in "Te'st", the generated code would have been:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Te''st%'')

and yes, it does get an "unclosed quotation mark" error.

I tried adding a COLLATE clause, but all collations have underscores, so Latin1_General_100_CI_AS became Latin1[_]General[_]100[_]CI[_]AS. D'oh!

We can't pass in either [ or % without those being wrapped in [ and ], BUT, we can concatenate stuff onto the N'% string, such as the output of a function. And, we can bypass the escaping of the [ and % characters by passing them in as hex / VARBINARY values, and then CONVERT them back into NVARCHAR once inside.

So, when filtering on Stored Procedures, we are starting with normally generated code of:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1

In order to make this work, we need to pass in a string having the following structure (in place of "Test"):

' + CONVERT(NVARCHAR(MAX), 0x.....) AND 'x' <> '

We could use -- at the end instead of AND 'x' <> ', but the AND construct ensures that if there is additional parts of the query that following this part, and are on the same line, that it continues to function as expected.

To generate our "escaped" filter, we can use the following:

SELECT CONVERT(VARBINARY(MAX), N'[Tt][Ee][Ss][Tt]%'); -- must end with "%"
-- 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500

Pasting that hex value into our SQL Injection work-around, we get:

' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '

If we set our filter to the value directly above, the generated code will be:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%'' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND ''x'' <> ''%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'1'

And the generated code is passed into sp_executesql. The statement that is executed shows up in Profiler as:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N'%' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '%')

This technique works because 0xYYYY.... is not something that the parser is looking for. It's only looking for the literal values of [, _, and %, and we aren't passing those in. We are just passing in something that will be translated into those once the Dynamic SQL is executed.

But it doesn't need to be a VARBINARY string that is passed in. Any function that passes back a string will work. You just need to concatenate the ending of N'% with something, followed by what comes just before %'), hence: ' + {something_ending_with_%'} AND 'x' <> '. I could have used + NCHAR(91) + N'Tt' + NCHAR(93) + to represent [Tt], but that seemed a lot bulkier than a continuous VARBINARY literal. However, if you just want a %, then doing + NCHAR(37) + N' would be just fine. Remember to use NCHAR() and prefix literals with N since this is all NVARCHAR data.

Attempt Tres (Mucho Yes)

So, you don't want to have to create the [Tt][Ee][Ss][Tt] string each time, and convert it to VARBINARY. I get that. Oh, and you have no access to add / change objects in SQL Server. Ok. But you do have access to tempdb, right? If you can create a function in tempdb, then this can be even easier. Just execute the following:

USE [tempdb];
IF (OBJECT_ID(N'dbo.ObjectFilter') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.ObjectFilter;
END;

GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
  IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
  BEGIN
    RETURN 1;
  END;

  RETURN 0;
END;
GO

Then set your filter to:

' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '

Generated code will be:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N'%' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '%')

You will have to adjust the column alias since tables uses tbl.name instead of sp.name. But now you can pass in any string and it will compare using a case-insensitive collation!!

And, this function will stick around until the instance is restarted. So you will only need to execute the above T-SQL every once in a while.

{ drops mic ... walks off stage } 😎

Additional Notes:

Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. Please see the "UPDATE" section at the bottom for links to the bug report I filed about this, as well as the enhancement suggestion I filed requesting a "case-insensitive" check-box, per filterable property.
From Randi's answer:

  Unless you find where the query is stored and change it, ...

There is no point in looking for the queries. They are coming from the DLLs that make up SSMS. I think C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.Sdk.Sfc.dll might be one of them, but not sure.
From Bob's comment on the question:

  SSMS does not enforce case-sensitivity unless server collation is case-sensitive.

Well, it depends. There are two levels of collations — instance-level and database-level — and the one that matters depends on what meta-data someone is trying to filter. Instance-level collation affects filtering instance-level meta data (e.g. Logins, Linked Servers, etc) and meta data in system databases. Database-level collation affects filtering user database meta data (e.g. Users, Tables, etc). In a Contained / Partially-Contained database, filtering the database-level meta data will always use the collation: Latin1_General_100_CI_AS_WS_KS_SC.

This separation of levels explains the following finding noted in Randi's answer:

  It also appears to not be possible on CS DB's on CI Servers:

From Elaskanator's comment on the question:

  Currently it is set to a binary collation (Latin1_General_BIN) which is case-sensitive.

Just FYI: this is, unfortunately, a very common misconception, but No, Binary Collations are not Case-Sensitive

UPDATE

I have submitted the following feedback items to Microsoft:

BUG: SSMS: Object Explorer Filtering allows for SQL Injection (oops)
SUGGESTION: SSMS: Allow forcing case-insensitive matching in Object Explorer filters


DB2: database hangs while trying to execute simple select query on specific table No errors or warnings, database just hangs while trying to execute simple select query on specific table. Other tables are accessible until I trying to query specific (faulty) table.

This should not be related to large volume of data, table contains only few thousands of rows.
I've tried db2 restart db2 dbName, but it didn't help.

Something wrong with data? Any suggestions how to recover?

DB2 9.5.3 on Solaris

I am not sure if I understand your problem. You say that the database hangs on a query, that means that all tables, views, etc, are blocked. However, you say that other tables are accessible, that means that the database is not hung, it is just an access to one table.

You used "db2 restart db", however it is for other things, for example when you need to restart the database once it has crashed, but I think your db has not crashed, just it is blocked.

I think your problem is a concurrency problem, the database has to be processing a big query on the table, putting an exclusive lock (or something similar) and the other queries cannot read the data with the current isolation level.

In order to see if this is the problem, try to get snapshots to see which table are accessed and check the lock list.

You can change the isolation level in the query, for example adding "WITH UR" at the end, if this isolation level is appropriated.

Other problems "could" be related to transaction logs (flush issue), busy or not enough agents, prefetchers issue, small bufferpool, expensive access plan. Well, you have to see the db2diag to have more clues about your problem.


Way to find when system health file is rolling over Is there a way to find the events when system health extended event files are rolling over rather than manually monitoring for the events?

For my medium load server they stay upto 2-3 days. But for heavy loaded servers these files are rolling every 15 mins or so but no fixed pattern or timings. We know the reason why and working to filter out unwanted events or ones reported as issues.

I am curious if there is a way we can query at what time would the roll-over of files is happening. I do not see much documentation on MS docs as well but cant find this info?

Please suggest if its possible and how?

You can get all the system_health event files and their oldest event like this:

DECLARE @file_name AS nvarchar(max);
DECLARE @file_path AS nvarchar(max);

SELECT
    @file_name =
        CAST(st.target_data AS xml).value(
            N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)')
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st
        ON s.[address] = st.event_session_address
WHERE
    st.target_name = 'event_file'
    AND s.[name] = 'system_health';

SELECT @file_path = LEFT(
    @file_name,
    LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1);

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event
FROM sys.fn_xe_file_target_read_file
(
    @file_path + 'system_health*',
    null, null, null
) files
GROUP BY files.[file_name]
OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649);

Note: for versions of SQL Server that don't support the NO_PERFORMANCE_SPOOL query hint (prior to SQL Server 2016), you can replace that with with QUERYTRACEON 8690 (see Spool operator and trace flag 8690 for details).

Hat tip to Erik Darling for suggesting the query hints, which sped things up significantly in my tests

The dates / times returned by that query are in UTC.  You could use an approach like this one to convert to server local time:

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event_utc,
    SWITCHOFFSET
    (
        MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetimeoffset')),
        DATENAME(TzOffset, SYSDATETIMEOFFSET())
    ) AS oldest_event

The code above

gets the filename for the currently-active event file,
then attempts to extract the path using this method,
then uses the sys.fn_xe_file_target_read_file dynamic management function to get oldest event from each file

So one way to accomplish your goal would be to run that query as a scheduled agent job, and log the results to a table.  Then you'd be able to see when the "oldest event" changes for each file (AKA when the file rolls over).

Recall that files can roll over for a number of different reasons.

If performance is an issue, and you are comfortable with PowerShell, you are likely much better off using the approach offered by Dan Guzman here.


Can you slow down queries for a specific user in a postgres database I am assuming it's possible to slow down the query access of a specific user in a postgres database. How would you do It? Is there a way for the user to see that such a setting has been placed on them, and what might the user do to overcome it, if anything at all.

Basically I'm interested in throttling resources on a per user basis to prevent bad actors from abusing the system.

To my knowledge, vanilla Postgres does not natively offer these features within the engine.  Per the Priorities PostgreSQL wiki page:

  PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others.  It's necessary to use operating system facilities to achieve what limited prioritization is possible.

As the last sentence alludes to though, there are ways to simulate this behavior by using connection settings (e.g. work_mem and maintenance_work_mem for memory) or OS level tricks with the help of extensions (e.g. prioritize for CPU prioritization).

If you've got a budget, there are customized Postgres engines that claim to provide this functionality as well, one of which is offered by EnterpriseDB.  I haven't used it nor do I have any idea how well it performs, but it's another option if you're looking for more alternatives.


Specify windows account for SSRS data source in SSDT project In the properties of a data source in SSRS, I can specify a Windows domain account to use, like this:

This data source was originally deployed from a SSDT project and I need these settings to be reflected in that project - otherwise the data source will no longer work correctly the next time that the project is deployed.

Unfortunately SSDT does not seem to provide a way of doing this.  If I specify credentials here it automatically assumes SQL Server authentication (which is of no use to me).

Am I missing something, or am I once again the victim of Microsoft's inability to be consistent?

There is no option to set a data source within the client tool to use a different Windows account. This is by design and is consistent with how Microsoft designed most all client tools (e.g. works the same way in BIDS, SSMS, etc.).

Your only option I know of would be to simply not redeploy the shared data source.


How to calculate total distance by datetime This is my database table ↓↓

ID   GPSLat     GPSLon       GPSTime
(1 , 3.214502 , 101.638419, '2017-06-23 12:21:51'),
(2 , 3.214478 , 101.638101, '2017-06-23 12:20:42'),
(3 , 3.214668 , 101.638049, '2017-06-23 13:25:53'),
(4 , 3.215089 , 101.638022, '2017-06-23 13:19:12'),
(5 , 3.215196 , 101.638626, '2017-06-23 14:10:56'),
(6 , 3.215255 , 101.639087, '2017-06-24 15:10:48'),
(7 , 3.215011 , 101.639448, '2017-06-24 15:10:36'),
(8 , 3.215041 , 101.640151, '2017-06-24 16:10:26'),
(9 , 3.218021 , 101.620416, '2017-06-24 17:31:59'),
(10, 3.123456 , 101.555555, '2017-06-25 18:45:41'),
(11, 3.345214 , 101.632157, '2017-06-25 18:02:51');

the distances result in create view image  ↓↓

I'm using the following query to sum up the distances by prev_ to curr_, but how do I calculate the distances by date or time instead of using prev_ and curr_?

For example, I want to calculate from date "2017-06-23 12:00:00" to "2017-06-23 14:00:00", which is output came from id1 to id4, how do I calculate and using the following similar query?

PS:I tried LIKE operator but it's looks tricky

select
 sum(distance_km) total_km
from
  distances
where prev_ >=2 and curr_ <=11;

this is my formula to calculate between each id distance  ↓↓ (I'm not sure the code it's whether affect the datetime , so I post it out too)

create view distances as
select
  prev.id prev_
, curr.id curr_
, prev.gpslat p_lat_
, prev.gpslon p_lon_
, curr.gpslat c_lat_
, curr.gpslon c_lon_
, prev.gpstime p_time_
, curr.gpstime c_time_
, ROUND( 6353 * 2 *
    ASIN(SQRT( POWER(SIN((curr.GPSLat - abs(prev.GPSLat)) * pi()/180 / 2),2)
  + COS(curr.GPSLat * pi()/180 ) * COS( abs(prev.GPSLat) *  pi()/180)
  * POWER(SIN((curr.GPSLon - prev.GPSLon) * pi()/180 / 2), 2) )), 2) as distance_km
from gpsdata prev
  join gpsdata curr
    on prev.id = curr.id - 1
where curr.id >= 1;

Edited: Expected Result

  select datetime from 2017-06-23 12:00:00 to 2017-06-23 14:00:00

prev_ | curr_ | p_time_             | c_time_             | distance_km
1     | 2     | 2017-06-23 12:21:51 | 2017-06-23 12:20:42 | 0.04
2     | 3     | 2017-06-23 12:20:42 | 2017-06-23 13:25:53 | 0.02
3     | 4     | 2017-06-23 13:25:53 | 2017-06-23 13:19:12 | 0.05

total_km
0.11


How to filters data with two windows conditions? I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I want to find the multiple access for the same user that have at least access with no door and at least one access with one door set.
Here is the SQL, that work. But it is very complicated. How could I improve it?
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS doors CASCADE;
DROP TABLE IF EXISTS access CASCADE;
CREATE TABLE users(id SERIAL PRIMARY KEY);
CREATE TABLE doors(id SERIAL PRIMARY KEY, type TEXT NOT NULL);
CREATE TABLE access(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id),
  door_id INT, FOREIGN KEY (door_id) REFERENCES doors (id)
);
INSERT INTO users VALUES (1), (2), (3), (4);
INSERT INTO doors(type) VALUES ('front'), ('back');
INSERT INTO access(user_id, door_id)
VALUES
  (1, 1), -- user have only access to door
  (2, null), -- user with empty door access
  (3, 1), (3, null), -- user with access and empty access
  (4, 1),(4, 2) -- user with access to front and back access
  ;

--- We want all the door access for the same user with at least one empty access and one door access

WITH users_with_multiple_access AS (
  SELECT access.user_id
  FROM access
  GROUP BY access.user_id
  HAVING (COUNT(access.user_id) > 1)
),
users_with_multiple_access_and_without AS (
  SELECT access.*, CASE WHEN door_id IS NULL THEN 1 END AS marked
  FROM access
  JOIN users_with_multiple_access USING(user_id)
),
users_with_multiple_access_marked AS (
  SELECT access.id AS access_id, sum(marked) OVER (PARTITION BY access.user_id) AS marked
  FROM access
  LEFT JOIN users_with_multiple_access_and_without uwmaw ON uwmaw.id = access.id
)
SELECT access.*
FROM access
JOIN users_with_multiple_access_marked awmam ON awmam.access_id = access.id AND marked IS NOT NULL;

Fiddle

Another way to do it would be:
select id, door_id, type
from (
    select u.id, a.door_id, d.type
         , count(case when a.door_id is null then 1 end)
               over (partition by u.id) as x1
         , count(case when a.door_id is not null then 1 end)
               over (partition by u.id) as x2
    from
    users u
    left join access a
        on a.user_id = u.id
    left join doors d
        on d.id = a.door_id
) as t
where x1 > 0
  and x2 > 0;

I hijacked @McNets Fiddle and added this query to it


mysqldump locks all tables even when specifically grabbing a few? I'm using a mysqldump command pulling data from 3 tables of 7. Are the other 4 locked as well?

Use --single-transaction option, and it won't lock any, provided you are using a transaction-aware storage engine (pretty much anything except MyISAM and MEMORY engines)


Mysqldump.exe Unknown option --no-beep When I try to run the command :

"C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqldump.exe" -u root -p******* my_database > backups/my_database_backup_20151221121320.sql

it says

mysqldump: unknown option '--no-beep'

(I'm on windows.)

What I have tried ?

I read and tried many results in google (and here) about moving the "no-beep" away from the [client] section in my.ini . Did not help. still the same error.
I tried to check if the my.ini file that I change, is the file that actually loaded - and it is, I can change other settings and they are applied.
I tried running the command without the full path (Only mysqldump -u..), still the same error.
I searched for all the instances of "beep" in the my.ini file
also, when I type mysql --help it always shows no-beep = TRUE , no matter if I comment or uncomment the "no-beep"

EDIT :

using --no-defaults I have no problem, but I don't want to change the command.

Some days ago my php script worked fine, I don't know what happened.

Find all my.ini file
Replace no-beep from everywhere (not only client) 'coz there is no such option.
But to do a temporary fix you can use no-defaults as follows:

"C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqldump.exe" --no-defaults -u root -p******* my_database > backups/my_database_backup_20151221121320.sql


How to model an “is a” relationship in an ERD using Crow’s foot notation? I have to create an entity-relationship diagram (ERD) for an assignment. I am doing this in a particular drawing tool with the Crow’s Foot notation. There is however one thing I cannot figure out:

How would you present an “is a” hierarchy in Crow’s Foot notation?

Currently, I am showing this by means of a one-to-one relationship between the Product superentity type and its subtypes (Books, Papers, etc.).

Is this the correct way to go or is it wrong? (I could not find any website talking about it).

In ER modeling, IsA relationships go by the name "generalization/specialization".  A search on this should give you a few pointers.  Diagramming this pattern requires using a notation that was originally called "Enhanced ER modeling", although it's been a long time since this extension was introduced.

Here's the wikipedia article:

http://en.wikipedia.org/wiki/Enhanced_entity%E2%80%93relationship_model

and here are some sample diagrams, from googling "Enhanced ER Images"

https://www.google.com/search?q=enhanced+er+images&rlz=1C1CHFX_enUS586US586&espv=2&biw=1600&bih=775&tbm=isch&imgil=E75HgoIGspploM%253A%253B5D6WMu1kSMfWrM%253Bhttp%25253A%25252F%25252Fwww.siue.edu%25252F~dbock%25252Fcmis450%25252F4-eermodel.htm&source=iu&pf=m&fir=E75HgoIGspploM%253A%252C5D6WMu1kSMfWrM%252C_&usg=__MdUOOzgIiqsPSTBQjFkTyRuaAlQ%3D&dpr=1&ved=0CDEQyjc&ei=TpMBVeDoKMOjgwSerIOYBg#imgdii=_

I generally use the triangle notation.


Why is my RDS mysql slow query log full of server restarts? One of my RDS mysql databases is giving me warnings about the slow query logs taking up significant space.

The logs are rotated once an hour, and contain a dozen repetitions of:

/rdsdbbin/mysql/bin/mysqld, Version: 5.6.13-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

What could be causing this?

This is probably not cause for concern.

MySQL writes a new header to the file each time the logs are flushed.  Presumably this is just in case you rotated the log file, so the new file will have a header... but it doesn't actually check whether it's a new file or not.  The server does not have to restart to write this entry, so it doesn't mean the server is necessarily restarting.

The rdsadmin user you see in the processlist appears to be the supervisory connection that Amazon uses to monitor and manage each instance.  Something -- presumably that connection -- periodically rotates the log files, most likely with some variant of FLUSH LOGS;.  It sounds like the flush occurs more often than the rotate, which would exactly explain what you're seeing.

mysql> SHOW STATUS LIKE 'uptime';

This will give you the actual uptime of the instance in seconds.  If that value is high, this is just the server writing a new header when the logs are being flushed to disk.


Cannot insert the value NULL into column I have a script for scripting out user permissions I use on SQL 2014 that works fine but when I try on SQL 2008, I am getting the following error:

Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'UserName', table '@DB_USers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(0 row(s) affected)

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT

dbname,username ,logintype ,create_date ,modify_date ,

STUFF(

(

SELECT ',' + CONVERT(VARCHAR(500),associatedrole)

FROM @DB_USers user2

WHERE

user1.DBName=user2.DBName AND user1.UserName=user2.UserName

FOR XML PATH('')

)

,1,1,'') AS Permissions_user

FROM @DB_USers user1

GROUP BY

dbname,username ,logintype ,create_date ,modify_date

ORDER BY DBName,username

I didn't take a really hard look at this but you're defining the DBName, UserName, and LoginType columns in @DB_USers as sysname. The type sysname is defined as NOT NULL.

What is SYSNAME data type in SQL Server?

As to why you're getting a NULL value you're going to have to check the dataset you're running against.


Clear SQL Server Buffer after SSIS is done? I'm new...

I've built an ETL process in SSIS that takes 46 flat files and loads them into stg.Suppliers and triage.Suppliers.  Then another solution runs that applies Business Rules.  And then the final solution moves the good stuff from stg.Suppliers into pdw.Suppliers.

My question is when I run all this (once a week); is it good (best practice) to clean up the buffer for the stg.Suppliers table?  I notice that when I run this query that it's the number one cached_pages_count.

If yes, how?

Leaving it in cache will have no downsides since SQL Server does a great job managing the cache without interference.  In fact, it may be beneficial to keep some of those pages cached.

Are you attempting to solve some problem?


Database Normalization Advice I have database for multi store application. So, I have 3 type of user thats Admin, Customer, Merchant and I have 3 table for users. User table, Customer table, Merchant table.

User Table;

id
username
name
password
email
status
city_id
state_id
role_id
...

Customer Table;

id
taxNumber
cart_id
user_id

Merchant Table;

id
taxNumber
commission
user_id

So, there is a 1:0 relationship between merchant/customer tables and user table. I want a 1:1 relationship but if I do this, I have to create columns like customer_id and merchant_id and if customer_id is not null then merchant_id will be null or merchant_id is not null then customer_id will be null in user table. Is it bad for normalization?

With the current design you could have several customers with the same user_id (similarly for merchants). This is a one-to-many association. To solve this problem, simply do not introduce id for table customers, but use user_id as primary key for it, in addition to it being a foreign key for table users. Then you can have only one customer for user. Similarly for table merchants.

Then you can enforce the constraint that users cannot exists without either being a customer or a merchant by defining in your application  functions to create customers or merchants, but not users. Those functions will create the appropriate rows. Finally, is up to you to decide if a customer can be also a merchant or not. In both cases, the function to create the customer or the merchant must take appropriate action.


What are the valid formats of a PostgreSQL schema name? I can't seem to find documentation that describes the valid formats of a PostgreSQL schema name. I know that a schema name cannot:

start with a number
have spaces
start with pg_

What else? Where should I look?

Per the fine documentation, I think this might be what you're looking for.

  SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable...


why processlist list shows limited threads Actually I have set max_connection value set to 500 but processes shows only 266 i.e when I run
show full processlist it shows only 266 processes not more than this however my connection value is 500.
I analyzed every query and found that every query is using single table there is no query which is using 2 tables.
Is any other settings which I am missing? Please suggest on this.Why it is showing only 266 processes?

I interpret your results to say that only 266 connections existed at that moment.  (If there aren't 500 users trying to connect, there won't be 500 rows in the processlist.)
Were you getting some error from other attempted connections not being accepted?  If so, what was the exact text of the error message.
266 connections, each running a query, is a problem.  I highly recommend you find the full text of the query (or queries) involved.  SHOW FULL PROCESSLIST may be useful if the queries were truncated.  Then show us the query, EXPLAIN SELECT ... and SHOW CREATE TABLE.  It is likely that some new INDEX (possibly composite)` would make them run faster -- and you might never see as many as 266 again.


Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB As far as I know in Microsoft SQL Server it is possible to use CURSOR to "scan" database and get all tables/entries where specified string is mentioned. Can I do this in Oracle 11g DB? Any caveats/limits for such scenario?

I'm hard-pressed to imagine how you'd accomplish that with a single cursor in SQL Server.

The same question has been asked and answered on StackOverflow.  If you want to get really tricky, you could also use an XML Query to search across objects with a single query.

In either case, the result is going to be insanely slow since you're full scanning every table at least if not once per character column.


InnoDB - Use combined index with primary key on GROUP BY As far as I know, an InnoDB index always contains the primary index.

I cannot get the following to work:

SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c

I have these keys:

t1.id = PRIMARY KEY
(b,c) = key

When I EXPLAIN this I get "Using where; Using temporary; Using filesort".

I do use my index on (b,c) but it doesn't use the primary index in that index to get rid of the temp table and filesort.

When I change the GROUP BY to GROUP BY c, I get rid of the temp table and filesort.

Added the primary key field to key (b,c) did not help either.

What am i doing wrong?

Update

Actual query:

SELECT q.id AS q__id, q.user_id AS q__user_id, q.title AS q__title, q. NAME AS q__name
     , COUNT(r.id) AS r__0
FROM questionnaire q
  LEFT JOIN response r
    ON q.id = r.questionnaire_id
WHERE ( q.user_id = 2 )
GROUP BY q.id
ORDER BY q.updated_at DESC ;

Tables:

CREATE TABLE `questionnaire` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `thankyou` text COLLATE utf8_unicode_ci,
  `status` enum('inactive','active') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'inactive',
  `published_at` datetime DEFAULT NULL,
  `license_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique_name_idx` (`user_id`,`name`) USING BTREE,
  KEY `questionnaire_license_id_license_id` (`license_id`),
  KEY `deleted` (`deleted_at`),
  KEY `updated_deleted` (`user_id`,`updated_at`) USING BTREE,
  CONSTRAINT `questionnaire_license_id_license_id`
    FOREIGN KEY (`license_id`)
    REFERENCES `license` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `response` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `questionnaire_id` int(10) unsigned NOT NULL,
  `ip` varchar(39) DEFAULT NULL,
  `headers` text,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `response_questionnaire_id_questionnaire_id` (`questionnaire_id`),
  CONSTRAINT `response_questionnaire_id_questionnaire_id`
    FOREIGN KEY (`questionnaire_id`)
    REFERENCES `questionnaire` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

Explain for GROUP BY Id
Explain for GROUP BY update_at

Because you only select columns from table_1 and because table_2 is only joined (on the right side) of the LEFT JOIN and none of its columns is used elsewhere (SELECT, WHERE, GROUP BY or ORDER BY clauses), you can completely remove the LEFT JOIN table_2 t2 on t1.id = t2.refid part.

This might have given less rows in the result but because you group by the Primary key of table_1 after the join, there is no such case.

SELECT t1.a
FROM table_1 t1
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c ;

Now, because you group by the Primary key of table_1, which is redundant when you have only one table, you can also remove that part: GROUP BY t1.id

Finally, the query is equivalent to:

SELECT t1.a
FROM table_1 t1
WHERE t1.b = 99
ORDER BY t1.c ;

which should use the index on (b, c), which yes, includes the primary key. You may consider it to be (b, c, id) if you want.

But whether that index will be used is not 100% sure. Depending on the selectivity of column b (what percent of the whole table has b=99?) this index may or may not be used. If there a lot of rows with b=99 (a large percentage), the optimizer may choose to scan the whole table instead and do a filesort, than use the index, select those (99,c) combinations that exist and then hit the table to find the a values.

If you have an index on (b, c, a) or on (b, c, id, a), the query will be able to find all the info that is needed in this index and in the correct order, so it will use it.


Join conditions and Where conditions in Left Join STUDENT table contains a list of students in my school Database

LIBRARY_BOOK table contains the information of each Book that is with currently with any student.

each student is allowed only 1 book at a time.

I want to know

Library Book with each Class 7 Student.

I am using Microsoft SQL Server 2008 R2

I have few queries which looks almost similar but don't behave in same way.

Query 1 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
WHERE ST.Class = 7

Query 2 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
AND ST.Class = 7

Query 3 :

Select *
FROM STUDENT ST
LEFT JOIN LIBRARY_BOOK LB
ON ST.Student_Id = LB._Student_ID
AND ST.Class = 7
WHERE ST.Class = 7

Can someone explain the difference between these queries in terms of both Output and Performance.

Query 1 will show you All records from student with a Class value of 7, and then any related Library_Book records if they exist.

Query 2 will show you all records from student, and related Library_Book records if they exist and if the matching row in student has a Class of 7.

Query 3 will show you All records from student with a Class value of 7, and related Library_Book records if they exist.

I would expect query 1 and query 3 will give you the same results, and query 2 to give you  more rows.

Performance is impossible to talk about without knowing your table structure, distribution of data, indexes, and RDBMS.  I would not expect them to be very different though since your filters are all the same.


Is there any database with versioning (column based) built in? I'm wondering if there is a database (SQL or NOSQL) which provides versioning for for the records ( if the column/field/property is enabled for versioning). I'm developing an authentication software and I need to provide a log /"history" for a number of properties so I'm wondering if there is a built-in feature or I need to reinvent the wheel one more time.

SQL Server has Change Data Capture in it's Enterprise edition.

From msdn link to information:

  Change data capture records insert, update, and delete activity that
  is applied to a SQL Server table. This makes the details of the
  changes available in an easily consumed relational format. Column
  information and the metadata that is required to apply the changes to
  a target environment is captured for the modified rows and stored in
  change tables that mirror the column structure of the tracked source
  tables. Table-valued functions are provided to allow systematic access
  to the change data by consumers.


Need to migrate a SQL Server 6.5 Prod to SQL Server 2012 in VM We are planning to move our SQL Server shop to VM(VMware). We still have one SQL Server 6.5 in production. What would be my course of action/path to move/migrate 6.5 to virtualized SS 2012?

It's the data that needs to be moved. The rest can be scripted/re-written if a straight migration of a DB(view, SPs.....) is not practical.

You can update 6.5 to 2000, from 2000 to 2008 and from there go to 2012 but its much better to script out the schema and bcp the data as Kin suggests. As he seems to be to humble to link to his own post I'll do it for him: Restore SQL Server 2012 backup to a SQL Server 2008 database?


Log to record activity against a database I've been assigned to create a general log.

I need a log that captures what actions were taken, by whom, to the database. Regardless of whether it is a SELECT or an ALTER or etc.  We need to keep track of everything that users have been doing to the database.

I have already created a general log to my localhost as a "mouse lab".  However, there are no records inserted into the general log table.

What can you suggest I do?  I'm using MySQL.

I'm losing myself on this, and I have no idea what to do anymore.  I'm just a freshie, not even graduated.

You can enable general log by adding this line in my.cnf/my.ini file:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_general_log

[mysqld]
general_log=1
also You can set General log output by changing your my.cnf/my.ini file (MySQL configuration file) to either TABLE or FILE if TABLE is choosen then server will log all queries in general_log table of mysql schema otherwise in FILE option it will log in a file path mentioned.
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_log_output