Clustered Index Scan on partitioned table even when there is covering index I have a partitioned table which is partitioned based on col1 int.  I also have a covering index for the query that I am trying to troubleshoot.

https://www.brentozar.com/pastetheplan/?id=BkNrNdgHm

Above is the plan

Left to its wishes SQL Server decides to do a clustered index scan of the entire table which is obviously slow.  If I force the index (like in the plan above) the query runs quickly.

What magic logic does SQL Server use to decide that the covered index is not useful?  I am not sure if top/orderby and rowgoal has anything to do with it.

My table structure is

Create table object2(col1 int, col3 datetime, col4 int, col5, col6 etc) clusterd on col1

nonclustered non aligned index is on col3,col4 (col1 is clustered so its included in nonclust)

SELECT top(?) Object1.Column1
    FROM Object2 Object1 WITH (NOLOCK,index(Column2))
    WHERE  Object1.Column3 >= ?
    AND Object1.Column4 IN (?)
    ORDER BY Object1.Column1

Edit added Repo

    CREATE PARTITION FUNCTION [PFtest](int) AS RANGE RIGHT FOR VALUES (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000)
    GO
    CREATE PARTITION SCHEME [PStest] AS PARTITION [PFtest] all TO ([PRIMARY]);
    GO

    create table test([ID] [int] IDENTITY(1,1) NOT NULL primary key clustered,[Created] [datetime] NULL,[Type] [int] NULL,text1 varchar(10),text2 varchar(20))
    on pstest(id)
    set nocount on

    declare @a int =1
    declare @type int
    while 1=1
    begin
    if @a%30 =0
    insert into test (Created, Type, text1, text2) select getdate(),4,'four','four'
    else
    insert into test (Created, Type, text1, text2) select getdate(),1,'one','one'
    set @a=@a+1
    end
    create nonclustered index ncl1 on test(created, type)

select min(created),max(created) from test
--2018-08-02 22:46:40.187   2018-08-02 22:49:01.577
SELECT top(10) ID
    FROM test
    WHERE  Created >= '2018-08-02 22:49:01'
    AND Type IN (1, 4)
    ORDER BY ID -- clustered index scan

SELECT top(10) ID
    FROM test
    WHERE  Created >= '2018-08-02 22:49:01.577'
    AND Type IN (1, 4)
    ORDER BY ID-- index seek of ncl1

Yes it is most likely a row goal issue.

SQL Server assesses that scanning the clustered index (to avoid a sort) will find the first ? matches quicker (at which point the scan would stop) than searching the nonclustered index for matches, sorting those, then returning the top ? matches.

On your version of SQL Server, you can test if setting a row goal is the cause by running the query with OPTION (QUERYTRACEON 4138) to disable row goals.

Related Q & A:

Why is my index not being used in a SELECT TOP?
How (and why) does TOP impact an execution plan?
Why is this query not using my nonclustered index, and how can I make it?


Prevent DROP COLUMN in PostgreSQL Can I stop DROP COLUMN from succeeding on particular tables in PostgreSQL 9 or 10 or later?

My application makes some assumptions about columns being forever, not disappearing. I'm afraid of later programmers forgetting about this constraint.

I know triggers can fire to control INSERT, MODIFY, and DELETE of rows. But what about DDL? Can I protect against certain changes to the table structure?

Security might be one way, assigning permissions to users and roles. But these future programmers will be allowed to add columns, only dropping columns is forbidden.

I am not concerned about malicious activity. I'm defending against the eager-beavers who may move too fast to remember the bigger picture.

Event Triggers

There are event triggers to control DDLs, trapping for the event sql_drop.

Call CREATE EVENT TRIGGER. In the trigger function, check to see if the object being dropped is one of your important columns. If so, raise exception to roll-back transaction.

  The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see Section 9.28). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it's not possible to look them up anymore.

Example:

drop table if exists t;
drop event trigger if exists etg;
drop function if exists fetg();

create function fetg() returns event_trigger language plpgsql as $$
begin
  if exists (
    select 1
    from pg_event_trigger_dropped_objects() as t
    where
      t.object_type = 'table column' and
      t.object_identity like any(array['%.t.y', 'public.tt.zz']))
  then
    raise exception 'Columns t.y and public.tt.zz are important!';
  end if;
end $$;

create event trigger etg on sql_drop execute procedure fetg();

create table t(x int, y int);
alter table t drop column y;

More docs:
Events
Functions


Converting SQL SERVER Procedure to POSTGRESQL I have procedure in SQL Server to print all players of given country.
I try to convert it into plpgsql, but I get errors.

SQL Server Procedure

CREATE PROCEDURE kraj_pilkarze @kraj varchar(30)
AS
DECLARE @idkraj INT
DECLARE @nazwa_zespolu varchar(30)
SELECT @idkraj = id_kraj FROM kraj WHERE @kraj=nazwa
DECLARE kursor_pilk CURSOR
FOR SELECT imie,nazwisko, id_zespol, id_kraj FROM pilkarz
DECLARE @imie varchar(20), @nazwisko varchar(30), @zespol int, @d_kraj int
OPEN kursor_pilk
FETCH NEXT FROM kursor_pilk INTO @imie, @nazwisko,@zespol, @d_kraj
WHILE @@FETCH_STATUS=0
    BEGIN
    IF @d_kraj=@idkraj
        BEGIN
            SELECT @nazwa_zespolu = nazwa FROM zespol WHERE @zespol=id_zespol
            PRINT @imie+' '+@nazwisko+', '+@nazwa_zespolu
            FETCH NEXT FROM kursor_pilk INTO @imie, @nazwisko,@zespol,@d_kraj
        END
    ELSE
        BEGIN
            FETCH NEXT FROM kursor_pilk INTO @imie, @nazwisko,@zespol,@d_kraj
        END
    END
CLOSE kursor_pilk
DEALLOCATE kursor_pilk

Doesn't work function in PostgreSql

CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30))
RETURNS VOID AS $$
BEGIN
DECLARE p_idkraj INT;
DECLARE p_nazwa_zespolu varchar(30);
DECLARE p_imie varchar(20), p_nazwisko varchar(30), p_zespol int, d_kraj int;
SELECT INTO p_idkraj id_kraj FROM kraj WHERE p_kraj=nazwa;
DECLARE kursor_pilk CURSOR FOR
SELECT imie,nazwisko, id_zespol, id_kraj FROM pilkarz;
OPEN kursor_pilk;
FETCH NEXT FROM kursor_pilk INTO p_imie, p_nazwisko,p_zespol, d_kraj;
WHILE (FOUND) LOOP
    IF d_kraj=p_idkraj THEN
            SELECT INTO p_nazwa_zespolu nazwa FROM zespol WHERE @zespol=id_zespol;
            RAISE NOTICE '% %, %',p_imie,p_nazwisko,p_nazwa_zespolu;
            FETCH NEXT FROM kursor_pilk INTO p_imie, p_nazwisko,p_zespol,d_kraj;
    ELSE
            FETCH NEXT FROM kursor_pilk INTO p_imie, p_nazwisko,p_zespol,d_kraj;
    END IF;
END LOOP;
CLOSE kursor_pilk;
END;
$$ LANGUAGE PLPGSQL;

It's way easier in plpgsql:

CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$
DECLARE
  _r RECORD;
  _id_kraj int;
  _nazwa_zespolu text;
BEGIN
  _id_kraj := id_kraj FROM kraj WHERE nazwa = $1;

  FOR _r IN
    SELECT imie, nazwisko, id_zespol, id_kraj FROM pilkarz
  LOOP
    IF r.id_kraj = _id_kraj THEN
      _nazwa_zespolu := nazwa FROM zespol WHERE zespol = _r.id_zespol;
      RAISE NOTICE '% %, %', _r.imie, _r.nazwisko, _nazwa_zespolu;
    END IF;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

But the following even simpler one would do the exact same thing:

CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30)) RETURNS VOID AS $$
DECLARE
  _r RECORD;
BEGIN
  FOR _r IN
    SELECT imie, nazwisko, id_zespol, id_kraj, (SELECT nazwa FROM zespol WHERE zespol = p.id_zespol) nazwa
    FROM pilkarz p WHERE id_kraj = (id_kraj FROM kraj WHERE nazwa = $1)
  LOOP
    RAISE NOTICE '% %, %', _r.imie, _r.nazwisko, _r.nazwa;
  END LOOP;
END;
$$ LANGUAGE PLPGSQL;

And you can achieve the same thing straight without even creating a function:

DO $$
DECLARE
  _r RECORD;
BEGIN
  FOR _r IN
    SELECT imie, nazwisko, id_zespol, id_kraj, (SELECT nazwa FROM zespol WHERE zespol = p.id_zespol) nazwa
    FROM pilkarz p WHERE id_kraj = (id_kraj FROM kraj WHERE nazwa = $1)
  LOOP
    RAISE NOTICE '% %, %', _r.imie, _r.nazwisko, _r.nazwa;
  END LOOP;
END;
$$;


Environment variable for two oracle_home i have installed oracle binaries at two different location on Oracle Linux , /u01 & /u02
when i set environment variable for databases created for oracle_home in /u01 it works fine as i edited .profile but when i set environment variable for the databases created in /u02 it does not change the environment and i use to set it manually . Like , PATH, LIBRARY, CLASS PATH, ORACLE_BASE etc.

i have tried setting both the environment variable in .profile but it seems to be not working.

where do i make the entry to change it automatically ?

I suggest you don't set the environment variables in .profile. Instead use the oraenv utility to change the environment to suite the instance you want to connect to.


How do you count the occurrences of an anchored string using PostgreSQL? If I have a string in a column on a row in a table like this

1 2 2 2 2 2 2

How would I count the occurrence of a substring 2 inside the string. Assume nothing other than a space-delimiter of " ".

For the purposes of this, let's treat the numbers as substrings

Sample data

CREATE TABLE foo
  AS
    SELECT 1 AS id, '1 2 2 2 2 2 2'::text AS data;

TABLE foo
 id |     data
----+---------------
  1 | 1 2 2 2 2 2 2

You can solve this with a

FASTEST was the pl/perl method which I placed last on this list because it requires pl/perl, and is likely not needed for most workloads.
FAST A string function, such as one the pattern explained below

length(str) - length(*replace(str, replaceStr))
  / length(replaceStr)

Something that converts from a string to an array.
SLOW Something that converts from a string to a table.

Possible solutions

String

Using length and regexp_replace

Most RDBMS's provide some method to calculate substring occurrences like this,

SELECT length(data) - length(replace(data, '2', ''))
  / length('2')
FROM foo;

This method isn't applicable here because without an anchor, we can't be sure if we're replacing the substring inside of something-space-delimited. As an example, the above replaces the 2 in 329. We can remedy that by using regexp_replace to anchor the substring.

SELECT length(data) - length(regexp_replace(data, '\m2\M', '', 'g'))
  / length('2')
FROM foo;

Because we're not splitting on simple spaces (' ') though we could with more complexity, we may also want to accommodate sub-strings of different lengths like in this question. This is why we explicitly include / length('2'). That reduces to a no-op, but if we we're search for something that was longer than one character, it'd be required.

SELECT length(data) - length(regexp_replace(data, '\m42\M', '', 'g'))
  / length('42')
FROM foo;

Using ARRAY[]

Splitting into an ARRAY[]

Here we have to subtract one match splits a string into two fragments, and thus the occurrences is one less than the fragment counts: this xyx split on y, produces {'x', 'x'} and we want the length to be 1 corresponding to the occurrences of y.

SELECT array_length(x, 1) - 1
FROM foo
CROSS JOIN LATERAL regexp_split_to_array(data, '\m2\M') AS t(x);
-- un-anchored version for reference.
-- CROSS JOIN LATERAL string_to_array(data, '2') AS t(x);

Alternatively, we can use string_to_array to separate something that's space-delimited and then count the matches,

SELECT id, array_length(array_positions(x, '2'), 1)
FROM foo
CROSS JOIN LATERAL string_to_array(data, ' ') AS t(x);

Using a TABLE

Splitting into a table with regexp_split_to_table

Here we split the regex into a table.. In this method you're using GROUP BY and count().

SELECT id, x
FROM foo
CROSS JOIN LATERAL regexp_split_to_table(data, ' ')
  AS t(x);

 id | x
----+---
  1 | 1
  1 | 2
  1 | 2
  1 | 2
  1 | 2
  1 | 2
  1 | 2
(7 rows)

And, from there you can run regular SQL on it.

SELECT id, x, count(*)
FROM (
    SELECT id, x
    FROM foo
    CROSS JOIN LATERAL regexp_split_to_table(data, ' ')
      AS t(x)
) AS t(id,x)
GROUP BY id, x;

 id | x | count
----+---+-------
  1 | 1 |     1
  1 | 2 |     6

Using regex_matches

Here we get away from splitting and instead use the \m, and \M anchors for word-boundaries.

SELECT count(*)
FROM foo
CROSS JOIN LATERAL regexp_matches(data, '\m2\M', 'g');

Procedural Languages

Perl

This method turned out to the be the fastest overall,

CREATE LANGUAGE plperl

CREATE FUNCTION count_occurances(inputStr text, regex text)
RETURNS smallint
AS $BODY$
  scalar @{[ $_[0] =~ m/$_[1]/g ]}
$BODY$
LANGUAGE plperl
IMMUTABLE;

Summary and Performance Implications

Following the same format of data, performance implications can be obtained with

CREATE TABLE foo
AS
  SELECT
    1 AS id,
    array_to_string(
      ARRAY(SELECT trunc(random()*100+1)::int % 100 FROM generate_series(1,5000) AS t(x)),
      ' '
    ) AS data
;

Under these constraints, I found that the procedural method with plperl was the fastest. Next I found the following as being the fastest native method,

length(str) - regexp_replace(str, replacement, g)
  / length(replacement)

Keep in mind the tried and true method of string replacement outside of needing to anchor the string is still the fastest and most efficient native method however clunky it may be,

length(str) - replace(str, replacement)
  / length(replacement)

That said, the ARRAY[] method is massively faster than splitting to a table.


Default Constraints with Merge Replication I am using merge replication in SQL 2012.

Why can't you mark default constraints as NOT FOR REPLICATION? You can disable all default constraints for a merge article, but it is all or nothing so it doesn't seem to offer enough control.

How about this scenario,

The user inserts a record 'Shape' at a client
The 'Shape' has a default constraint to set the NumberOfSides field to 4
The client user edits the value of NumberOfSides and sets it to 5
They sync with the server

When they sync with the server won't it repeat the default constraint and set the value of 'NumberOfSides' back to 4?

Am I missing something here?

I don't want to disable the default constraints being replicated to the client either because there are valid default values that need to be set. The client side user needs to have these values set to use the software.

UPDATE:

The explanation about the default constraint being applied only once makes sense.

So my only remaining question is if I have default contraints which use sequences I am in trouble because sequences cannot be replicated. Are these my only two options?

Turn off all default constraints for a particular table (not ideal if some of them are not for sequences)
Create the sequences manually at the client side, and then create a custom handler to apply the correct sequence values at the server when syncing

When data is replicated from one server to another using SQL Replication all the values which were inserted into the row are sent along.  Default constraints only apply when no value has been specified for the column.  If the user specifies a value of 5 then 5 will be replicated and inserted into the table.


How to prevent informational messages from getting written when query results are output to a file When I am running the below query as a batch file (.BAT) using the Windows Task Scheduler, I am getting some informative messages in the output file DatabaseNames.txt in addition to the database names. How can I prevent those messages? These messages are not visible when running from the Command prompt.

"C:\Program Files\PostgreSQL\10\bin\psql.exe" --host localhost --username postgres -Atc "SELECT datname FROM pg_database WHERE datallowconn IS TRUE AND datname NOT IN('template0','template1')" > D:\Backup\DatabaseNames.txt

Below were the informative messages

Welcome to PostgreSQL!
Type :version to see PostgreSQL version
Type :extensions to see the available extensions
Time: 0.994 ms

Apparently "someone" create a psqlrc.conf file that contains those messages you are seeing.

You can prevent psql from loading psqlrc.conf by passing the parameter --no-psqlrc (or -X). To hide the any other messages use the --quiet (or -q) parameter.

So your batch file should contain:

"C:\Program Files\PostgreSQL\10\bin\psql.exe" --no-psqlrc --quiet --host localhost ...


is there a way to find out who was spid228 at a given time in the past? I have a script that reads the error log so that I can see what has been happening within the last day:

--create the table
CREATE TABLE #Radhe (logdate datetime not null default (getdate()),
                     processinfo varchar(108) not null default ('Radhe'),
                     the_text varchar(4000))

-- create a non-unique clustered index
CREATE CLUSTERED INDEX IXC_RADHE_RADHE ON #RADHE(logdate desc, processinfo  asc)

-- load the table
INSERT #Radhe EXEC xp_readerrorlog

-- read the data
SELECT * FROM #Radhe with (index(IXC_RADHE_RADHE))
WHERE LOGDATE > (GETDATE() - 1)
  AND processinfo LIKE 'SPID%'
order by logdate desc

when I run this script this morning I see the below:

  2016-04-13 11:08:23.640   spid228 Configuration option 'xp_cmdshell'
  changed from 0 to 1. Run the RECONFIGURE statement to install.

Is there a way I can find out who/IP of spid228 who did the above?

You can easily extract that information from the default trace:

DECLARE @path nvarchar(4000);

SELECT @path= path
FROM sys.traces
WHERE id = 1;

SELECT TextData, HostName, ApplicationName, LoginName, SPID, StartTime
FROM fn_trace_gettable(@path, DEFAULT)
WHERE TextData LIKE '%xp_cmdshell%';


What are the low hanging fruits of a SQL Server 2014 migration? I have read about new features and changes of the SQL Server 2014 version. But I still don't know the benefits every server will have by upgrading (from 2008 R2) to 2014?

Are there any new features / functionality (low-hanging fruits) every instance and database will benefit of immediately, without a single change to the database schema?

In 2014 you get the new cardinality estimator which could see your queries get better plans and therefore execute more efficiently.

A good reason to upgrade from 2008R2 is that it is on extended support meaning it is unlikely to receive anymore updates and only SQL Server 2012 SP2 + versions are able to operate with TLS 1.2 see here


why we can specify table names enclosing them with [] Is there a reason we can specify a tablename with [schema].[tableName] instead of just schema.tablename?

is it JUST to allow spaces in a tableName?

They're called Delimited Identifiers, and their primary purpose is to allow for special characters and keywords to be used in/as table names

For example, some genius decided the name of the primary table in a database I work with would be master, which is a keyword.

They're really not needed at all except in those cases.


Merge two records in the same table, keeping foreign key relationships intact for both I have a table, called SITES, that has three columns, lets say it looks like this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/

I also have a related table called Logs that looks like this:

ID  SiteID   LogData
1   1        --data--
2   1        --more--
3   2        --other--
...

The SITES table gets populated both by users of the system and also occasionally some batch processes. From the batch process, I don't always have the 'name' available, so a record is created that looks like this in SITES (when searching by Path yields no result), because the batch process is mostly interested in creating data in the Log table.

ID  Name        Path
99              http://arstechnica.com/

The problem situation arises like this:

1) User creates new SITE, but doesn't enter the Path (doesn't know it, or in some cases it doesn't yet exist) so the SITES table looks like this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/
3   Yahoo

2) The batch process comes along and needs to add a log for http://yahoo.com, searches SITES by Path and does not find it, so it makes its own and the result is this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/
3   Yahoo
4               http://yahoo.com

My question is, how can I merge Record 3 and record 4 while preserving the referential integrity of both records? Lets say for the sake of argument, that each record has several related records in both the Log table, as well as some other tables.

I understand that it will be a manual process of identifying records that need to be updated, so let any solution assume that I have audited the list and found all of the "duplicate" records.

This is a very straight-forward data cleanup task. Your solution is good (I'd recommend using a transaction construct as below, just to be safe) -- I've taken the same approach except I used set-based operations so it can be scaled up and automated once you come up with a better duplicate detection algorithm than the rudimentary one I provided.

/* Test setup */
CREATE TABLE [dbo].[Sites]
(
    Id int NOT NULL PRIMARY KEY,
    Name nvarchar(50) NULL,
    Path nvarchar(100) NULL
);

CREATE TABLE [dbo].[Logs]
(
    Id int NOT NULL PRIMARY KEY,
    SiteId int NOT NULL FOREIGN KEY REFERENCES [dbo].[Sites](Id),
    LogData nvarchar(MAX) NULL
);

INSERT INTO [dbo].[Sites](Id, Name, Path)
    VALUES
        (1, N'Google', N'http://www.google.com'),
        (2, N'Microsoft', N'http://www.microsoft.com'),
        (3, N'Yahoo', NULL),
        (4, NULL, N'http://www.yahoo.com');

INSERT INTO [dbo].[Logs](Id, SiteId)
    VALUES (1, 1), (2, 1), (3, 2), (4, 3), (5, 3), (6, 4);

/* Identify potential duplicates (note: very rough) */
SELECT
    s1.Id AS SourceId, s1.Name AS SourceName, s1.Path AS SourcePath,
    s2.Id AS DuplId, s2.Name AS DuplName, s2.Path AS DuplPath
    FROM [dbo].[Sites] s1
    INNER JOIN [dbo].[Sites] s2 ON
        (s2.Path LIKE (N'%' + s1.Name + N'%')) AND
        (s2.Id > s1.Id);

/* Merge duplicates */
DECLARE @duplicates table
(
    SourceId int NOT NULL,
    TargetId int NOT NULL,
    PRIMARY KEY (SourceId, TargetId),
    CHECK (SourceId != TargetId)
);

INSERT INTO @duplicates(SourceId, TargetId)
    VALUES ; /* Edit me! */

SET XACT_ABORT ON;

BEGIN TRANSACTION;

    UPDATE l
        SET SiteId = d.TargetId
        FROM @duplicates d
        INNER JOIN [dbo].[Logs] l ON l.SiteId = d.SourceId;

    UPDATE st
        SET
            Name = COALESCE(ss.Name, st.Name),
            Path = COALESCE(ss.Path, st.Path)
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] ss ON ss.Id = d.SourceId
        INNER JOIN [dbo].[Sites] st ON st.Id = d.TargetId;

    DELETE s
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] s ON s.Id = d.SourceId;

COMMIT TRANSACTION;

/* Validate the site attributes were merged correctly */
SELECT
    s.*
    FROM @duplicates d
    INNER JOIN [dbo].[Sites] s ON s.Id = d.TargetId;


Are there alternatives to the TOAD tool for SQL Server? Are there any great tools as an alternative TOAD to be used for SQL Server?

The tool can be commerical or open source.

SQL Server Management Studio that comes with SQL Server?

If you need to run on non-Windows, then look at SQuireeL


Which Postgresql Replication Solution to Use? (Asynchronous Multimaster / Bucardo?) First, I'm not a DBA, so pardon me if any of this question seems "off."

I've written a peer-to-peer multiplayer game (the client) which connects to one of multiple servers for match making.

Currently, there is only one server (a linode, let's call it Server 1) which runs the game's custom matchmaking process and PostgreSQL 8.4 (I will be upgrading this to 9.1, 9.2 or 9.3 if necessary).

The matchmaking process uses libpq asynchronously for all SQL statements. Statements are not too complex, so load balancing is not an issue.

I plan to add more linodes (call them Servers 2, 3, 4, etc.) that run the matchmaking process and PostreSQL as necessary. The challenge is that I want high-availability for all clients. If server 1 is unreachable, then server 2 can be used instead, with access to all the same data.

The original plan was to have all servers connect to Server 1's database and asynchronously send SQL statements via libpq. The problem there is that if Server 1 is ever temporarily offline or unreachable, then every other server will fail.

The "simplest" solution I can imagine is for each of the servers to completely mirror the database. If Server 1 is down, clients can connect to Server 2 which reads and writes to its own database, replicating any changes to Servers 3 and 4 immediately, and Server 1 once it comes back online.

In this fashion, every server would hold an entire "mirrored" copy of the database.

After reading through the introductory sections of PostgreSQL 9.3's documentation on replication, it seems like way to implement this solution would be asynchronous multi-master replication. (Is Bucardo the only choice here?)

The thing I'm worried about with asynchronous replication is SQL inserts. When a new client plays for the first time, a player database entry is created. If Servers 2, 3, and 4 are online and Server 1 is offline, will there be any issues with 1, 3 or 4 if 2 inserts a new player row? (Imagine 1 coming back online and immediately trying to insert another player row.)

Is asynchronous multi-master the right way to go for the above mentioned scenario?

Or, is there a simpler or easier solution that I am overlooking? Perhaps one that doesn't require middleware, but just uses PostgreSQL 9.3's built in replication?

If you're not a DBA - i.e. you're not experienced with the complexities of replication, failover, subtleties of transaction isolation, etc - then if possible do not use multi-master.

There's no multi-master clustering for PostgreSQL, only multi-master replication via 3rd party add-ons. These add-ons have a significant performance impact, but more importantly they don't deal with inter-node locking and transaction isolation. Concurrent transactions on different nodes can see different data, can both modify the same rows, etc.

Frankly, multi-master clustering is pretty underwhelming even in products that do have it available. Performance tends to be pretty poor and scale badly. This isn't a deficiency in the products, it's just that MM clustering is hard and you pretty much get to pick either correct or fast.

Personally, I recommend using two big nodes. Have one node running a streaming replica and the other running as master. If one goes down, fail over to the other. Tune your app and queries properly before adding more capacity.


Pause and resume execution of stored procedure There are two processes(p1, p2) that may run simultaneously. p2 is a scheduled execution of SPROC while p1 consists of a group of stored procedures that is triggered on the request.
Execution of p1 while p2 is in progress can create issues. Only one should run at a time.
There are three ways to solve this problem

When p1 starts check whether p2 is in progress and wait until it completes. p2 can run for more than a day and it might not be a preferable solution
Kill p2, complete p1 and then restart p2. Killing and restarting p2 isn't safe due to nature of the sproc
pause p2 and resume p2 when p1 completes

Questions
1. How can I search and stop store procedure being executed?
2. Is there any way I can pause and resume a stored procedure that is being executed?

There's no clean way to just pause and resume a stored procedure, but you can have it sleep for a fixed period of time using WAITFOR DELAY 'XX:YY:ZZ' (where X is replaced with hours, Y minutes, and Z seconds for how long you want the thread to sleep).
It really depends on what p2 is doing and if there's multiple batches of queries you can break up with a check for if p1 is running. If p2 is just one single long running query then you won't be able to pause it when p1 starts, you'd only be able to make p1 wait until p2 was finished. (In this case you shouldn't have a single query running as long as a day though, so I'd recommend optimizing p2 anyway.)
If you do have a way to break up the queries in p2 then you can periodically check if p1 is running between each batch of queries, use a WHILE loop and the aforementioned WAITFOR DELAY to sleep the thread (whatever time  amount is acceptable to you) of p2, checking if p1 is done running on each iteration of the WHILE loop.
I don't think there's a perfect solution if you're trying to avoid any overlap, but if some overlap of the jobs is ok then you should be able to rig something with the above up.
You might be able to help prevent any data overlap (at the cost of potentially making p1 wait a mininal amount of time) by using transactions around each batch of queries in p2 that way p1 will have to wait until p2 finishes it's active transaction. If you want to avoid overlap for performance reasons instead, then you'll need to implement a check system in p1 as well to loop and sleep while waiting on p2 to finish it's current query batch before it sleeps itself once it checks p1 is now running.


How to keep one query from slowing down entire database? Using MySQL 5.0.x, we have a process to generate benchmarks for our business that consolidates a bunch of data, then pull out data based on certain criteria. This process used to take 18 hours, it's been creeping up to over 24 hours, meaning it's now intruding on the business day.

The problem with this is that these queries substantially slow down the database. Both the INSERT ... SELECT queries and, even moreso, ANALYZE.

Is there some way to make a single query not slow down the entire system? Can MySQL throttle it to not bog down the whole dbms? If these could continue running during the business day I'd be very happy, but without that, ... I don't know because I've made this as streamlined as I can with our current system. We're testing moving to MySQL 5.5, and the improvements it gives (like InnoDB) but it would be nice to know if throttling was possible, or if there's some setting I'm missing that's causing a single query to drag down the system.

Unfortunately, as far as I know, there is no way to throttle either INSERT...SELECT or ANALYZE TABLE. I was going to suggest using INSERT DELAYED ... SELECT but came across this:

  DELAYED is ignored with INSERT ... SELECT.

So that's out the window. So, if you can't throttle these long-running queries, you're going to have to look at speeding them up so they don't take so long to complete.

I am assuming from one of your other questions that you are using MyISAM. This means your INSERT..SELECT will be locking the table you are writing to, so any attempt to access it will cause waits and long-running queries.

Speeding up the queries are going to depend on how fast your drives are, how much of your 16GB memory you are using effectively, etc. Without such detail, the rest is guess-work:

Do you really need to run ANALYZE so often? If so, how big is your key_buffer_size (again, assuming MyISAM)?
The SELECT portion of INSERT...SELECT is probably writing to a temporary file on disk, causing you to be IO-bound (waiting for the temporary disk file to be finished before even starting the INSERT portion). You can look at increasing max_heap_table_size and max_tmp_table_size to be larger than the size of your table you're selecting from (note MySQL will use the value that is least of these two variables).
You might be able to break the INSERT...SELECT statements into smaller chunk sizes so each one finishes faster, but it depends on your use case.

I'm sorry if this isn't much use at the moment, and will try to update as we get more information.


Always On Availability Groups Network Configuration in AWS I have set up a 3-node, multi-subnet Always On Availability group in AWS, which seems to be working well. However, each node only has a single network adaptor with 3 IP addresses assigned to it (1 primary, 1 WSFC IP resource, 1 listener). Is this sufficient for high-availability, or do I need additional networks? I believe with a traditional cluster you would need multiple networks & adaptors to ensure there is no single point of failure. I'm not sure if this is the case in AWS, as I assume they have redundancy built-in at their end?

In AWS EC2, you don't get the ability to add additional network ADAPTERS to your VM.

You can add additional network INTERFACES, which are just different IPs/subnets/routes/etc, but they're using the same underlying network adapters (cards).

You can choose to use instance types with faster networking ports, though. The EC2 instance configuration page lists which ones have 10 Gigabit ports, although I like ec2instances.info for easier sorting and filtering. When you're doing Always On Availability Groups, it's especially important to use this faster networking because high-network operations (like backups to another machine) can result in SQL Server timeouts (disclaimer: that's my blog post) if you've only got GbE networking. (That causes AG failovers.)


Cannot Connect Using SSMS to Local SQL Server 2008 R2 About six months ago my vendor installed SQL Server 2008 R2 into a clustered pair of servers, DB01 and DB02.
I've been logging on, periodically, to DB01 to audit some of the data contained in the database instance.
I've been using SSMS the whole time, and for six months there have been no problems.
Suddenly yesterday, I am unable to connect using SSMS.
I get the following error:

Cannot connect to DB01.
Additional information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible.  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53.)

This is very confusing, very little has changed since the last time I've logged in to SSMS.
TCP/IP is enabled on SQL Server, as are named pipes.
One thing I know has changed is that for the CLIENT machines that hit it, the TcpTimedWaitDelay parameter has been set to 30, and the MaxUserPort has been set to 10000.
But that's on the client machines only.
Could there be an issue arising from from these parameters not being mirrored on the database servers?
Could there be an issue arising from these two boxes sharing an IP for the clustering?  The database is normally accessed through a shared IP, where DB02 takes the IP if DB01 fails.
I've considered restarting the SQL Server service but that scares the bejeezus out of me.
What if this issue spreads to the application currently using the databases?

As I posted in the comments, a couple of possibilities exist for that error, mainly lack of memory for additional TCP connections (generally in conjunction with 32-bit OSes), or lack of TCP ports. Both issues (and possible solutions) are outlined here on the SQL Protocols blog:

Understanding the error "An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full."


MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done.

I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file growth, file_per_table is turned on).

But after about 5GB of data it slows down to the 2-4MB/sec range, as I get over 20GB it was down around 2MB/sec.

InnoDB buffer pools size is 8G.  And I've done the following prior to running the LOAD DATA INFILE command:

SET @@session.sql_log_bin=0;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
alter table item_load disable keys;
//Run LOAD DATA INFILE....

I can't see the reason why it's starting off well and slowing down over time.

Also, using the same settings, I ran the same LOAD DATA INFILE command with the table using InnoDB and MyISAM and a 5GB test dataset, MyISAM was 20x faster:

InnoDB:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (21 min 25.38 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

MyISAM:

mysql> LOAD DATA CONCURRENT LOCAL INFILE '/tmp/item' REPLACE INTO TABLE item_load;
Query OK, 2630886 rows affected, 6 warnings (1 min 2.52 sec)
Records: 2630886  Deleted: 0  Skipped: 0  Warnings: 6

Anything else I should consider trying? The MyISAM engine is able to keep up the load rate much better.

Additional details:

I've tried loading the files individually, no difference.
Incidentally, I have 150 files of 500MB each, within each file the keys are sorted.
After getting 40GB in overnight, 12h later, the load rate was down to 0.5MB/sec, meaning the operation is, practically speaking, impossible.
I haven't found any other answers to similar questions on other forums, it's seeming to me that InnoDB doesn't support loading large amounts of data into tables over a few GB in size.

OBSERVATION #1

I noticed you turned off autocommit. That will pile up so much data in ibdata1. Why?

There are seven(7) classes of information that is stored in ibdata1:

Data Pages for InnoDB Tables
Index Pages for InnoDB Tables
Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

Some of this info is made visible to certain transactions depending on the isolation level. Such actions could produce unintended primary key locks and lots of phantom data. As these two things increase, you should expect a fair slow down.

Recommendation: Leave autocommit on

OBSERVATION #2

I see you have this:

alter table item_load disable keys;

DISABLE KEYS does not work with InnoDB. Here is why:

MyISAM : DISABLE KEYS simply shuts off Secondary Index updating for the MyISAM table. When you mass INSERT into a MyISAM table with keys disabled results in a fast table load along with a building of the PRIMARY KEY and all unique indexes. When you run ENABLE KEYS, all Secondary Indexes are built linearly on the table and appended to the .MYD.
InnoDB : As shown in the internals picture of InnoDB, the system tablespave ibdata1 has  a structure dedicated to Secondary Index Insertions. At present, there is no provision to handle indexes the same as MyISAM.

To illustrate this, note my attempt to run DISABLE KEYS on an InnoDB table in MySQL

mysql> show create table webform\G
*************************** 1. row ***************************
       Table: webform
Create Table: CREATE TABLE `webform` (
  `nid` int(10) unsigned NOT NULL,
  `confirmation` text NOT NULL,
  `confirmation_format` tinyint(4) NOT NULL DEFAULT '0',
  `redirect_url` varchar(255) DEFAULT '<confirmation>',
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `teaser` tinyint(4) NOT NULL DEFAULT '0',
  `allow_draft` tinyint(4) NOT NULL DEFAULT '0',
  `submit_notice` tinyint(4) NOT NULL DEFAULT '1',
  `submit_text` varchar(255) DEFAULT NULL,
  `submit_limit` tinyint(4) NOT NULL DEFAULT '-1',
  `submit_interval` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table webform disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'webform' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

mysql>

OBSERVATION #3

You noticed that MyISAM loads 20x faster than InnoDB. Would you like that to be more like 24-25 times faster? Then run the following:

ALTER TABLE item_load ROW_FORMAT=Fixed;

This will speed up INSERTs times 20-25% without any other DDL Changes. Side effect: The MyISAM table can grow 80%-100% in size, possibly larger.

You could run this on an InnoDB table as well, but the ACID-compliant behavior and MVCC of InnoDB would still be the bottleneck of its performance, especially if VARCHAR fields increase significantly are are written to ibdata1.


Database normalization for sensor data I'm working on the backend of a quality evaluation tool. And I hit a the limit of too many columns.

SQLSTATE[HY000]: General error: 1005 Can't create table xxx.xxx (errno: 185 "Too many columns")

And because I've seen this question. Let me explain:
The company I work for is selling advanced sensors with algorithms in the firmware. With every new firmware, we want to compare it to the previous ones, to check if something still works, has improved or is now worse. For this, we have a number of test scenarios (200+). Each scenario consists of (400-2.000) measurements from which all are needed. I call a run through one scenario a record.
In my previous design I created a table for each record and I ended up with 50.000+ tables, which is not optimal, to say the least. In an attempt to normalize the data I created the following schema:

There is on table for each scenario (slug_1 and slug_2 here). And a idmap table to relate each record with the firmware and other important data (here shown as other_stuff). The problem now is, that I need 1-4 columns for each measurement in one scenario which means 8.000 columns max, which is a new problem.
Besides the id and the table name, there are no relations, nor search requirements on these large tables. Each request would consist of two rows from the same table to compare them.
Question:

Is there a better design to store the data?

I thought maybe I should store the measurements as json string or serialize the measurements array directly to binary. I could use a compression algorithm before storing and decompress the data after reading?

Are this many columns even that bad, if they are never searched?

If not, how can I get around the

errno: 185 "Too many columns"

Edit 1:
To answer some questions:

Are all the tests pass/fail, or do you need to store more detail.

Every measurement consists of two values at the moment. Ok (bool) and Ct (int). Ok just stores if there was an error or not. The sensor can provide ~10 different errors, why the test failed, but I'm reducing this to true/false for now. If there is a need to store the error, I'll create an error table and store the relation instead. The second value Ct stores the time needed for this measurement. It is possible that there will be a 3. value in the future, but for now this is not needed.

Does the type of detail depend on the test?

No, it doesn't in all scenarios the measurements are the same.

Is there a Product or Sensor these tests apply to?

Yes, I store all sensors, firmwares, and other things in separate tables like other_stuff and relate it with the idmap table to the records of the tested scenarios.

Here some info what I'm working with:
Laravel 8 with php eloquent on a mysql database.

Is there a better design to store the data?

Probably. A measurement seems to be an entity in itself, and should be modelled like one.  You will then need a one-to-many (or many-to-many, if a particular measurement applies to more than one scenario) relationship between scenarios and measurements.


Name of Mysql database folder I try to access to mysql data via filezilla and download to my pc,
What is name of folder where is my mysql data ?
I use ubuntu.

Thanks.

By default, most packages in most distributions in Linux, including official and distributions ones on Ubuntu set the default MySQL datadir on /var/lib/mysql. You can check the actual datadir by connecting to mysql and executing:
mysql> SHOW GLOBAL VARIABLES like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.02 sec)

Technically, there could be files outside of the datadir (per-table configuration, innodb files, etc.), but not with the default configuration.
Please note that, in general, that directory is owned by the mysql user, so you will not be able to read or write it as a regular user. Only the mysql system user or a user with administration privileges will be able to access there.
If you are using Filezilla, you probably will be using ftp/sftp/ssh. Please note that copying the files directly has the following restrictions:

You can only do it consistently if the server has been stopped. Otherwise you will end up with a corrupt set of files.
The files you will copy can only be used as a backup, or to clone the current setup, you cannot access the tables unless you set up a new mysql instance on your local pc
You may want to copy other files like logs (/var/log/mysql*) and the configuration files (/etc/my*)

If you want simply to access the mysql service/data remotely and operate with it, you do not need filezilla, only a mysql client, like the mysql monitor command line, MySQL Workbench or phpmyadmin, by setting them to connect to the remote host (assuming the service is available from the outside).
Update: if what you want is a text-based representation of your database, and you do not want to use a client like Workbench or phpmyadmin, please follow these steps:

In Ubuntu, execute on a command line prompt:
 mysqldump -u root --all-databases > ~/database.sql

Copy the database.sql file found on your home directory to your local pc using filezilla

If you want to export a single table and open it in an application like Excel, you can export it also in CSV format.


How to get list of tables, views etc. a users has rights to on Sybase IQ? I'm looking for a way to get a list of views, tables, procesdures etc, a user has been granted to. A select onto SYSROLEGRANTS helped me to find basic role of users -- but not more like this.

So e.g. Having this:

create or replace view myFancyView as
SELECT * from mytable;

GRANT SELECT ON myFancyView TO myUser;

I'm hoping for a list like that:

|User  | Right                |
|------|----------------------|
|MyUser| SELECT ON myFancyView|

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/X22708.htm lists the system stored procedure, sp_helprotect which provides a mechanism for displaying all the rights a user has in a given database.
From that document:

Displays all the permissions that “judy” has in the database:
sp_helprotect judy


'not exists' seems to be slowing insert I am trying to run a script that will batch insert over 20 million records into a table 10,000 at a time. At the start of the run it seems to be working fine. Although once a number of records had already been inserted (270,000) the script began to slow done considerably. It took 23 hours to insert another 30,000 records. My best guess is that as the number of new records increases the part where the script checks to see if the new record already exist is taking much longer. I have created indexes of the tables used in the script but I need shave off run time for this script. Any help would be much appreciated. My script is below.

CREATE NONCLUSTERED INDEX [plan2TMP] ON [dbo].[plan2]
(
       [l_dr_plan1] ASC
)
INCLUDE
(
l_address,
l_provider
)ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [nameTMP] ON [dbo].[name]
(
       [dr_id],
       [nationalid]
)ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [plan1TMP] ON [dbo].[plan1]
(
[dr_id],
[cmt]
)ON [PRIMARY]
GO

DECLARE @BatchSize int = 10000

WHILE 1 = 1
BEGIN

INSERT INTO plan2(
                     l_dr_plan1
                     , l_address
                     , l_provider
                     )
SELECT TOP (@BatchSize)
                     dr1.link
                     ,ad1.link
                     ,dr.link
from plan1 dr1
INNER JOIN name dr ON dr1.dr_id = dr.dr_id
INNER JOIN name2 dr2 on  dr2.nationalid = dr1.cmt
INNER JOIN address1 ad1 ON ad1.dr_id = dr2.dr_id
WHERE NOT EXISTS (
                  SELECT l_plan1
                  FROM plan2
                  WHERE ltrim(rtrim(dr1.link)) + ltrim(rtrim(ad1.link)) + ltrim(rtrim(dr.link)) = ltrim(rtrim(l_dr_plan1)) +ltrim(rtrim(l_address)) +ltrim(rtrim(l_provider))
                  )
AND dr1.cmt <> ''

IF @@ROWCOUNT < @BatchSize
  BREAK
END

The source data isn't really bad. I could pull out the ltrim/rtrim without encountering any problems. The reason I have the concatenation is that I couldn't come up with a better way to compare the values I was inserting to the values that had already been inserted.

The link value is a unique column in each table (but not the PK or FK). The plan2 table references the links of the plan1, address1, and name tables to connect those records in the back end and show them together in the front end.

The purpose of inserting 10k at a time was that the log file grew so large it brought the server memory to about 10 mbs. With the batch it will commit records in chunks and keep the log file from expanding out of control.

The code that you have now is guaranteed to do more work on each successive loop. I'll assume that the target table is empty at the start, so the SELECT part of the query only needs to return 10k rows in order to insert 10k rows into plan2. For the next loop the first 10k rows returned by the SELECT query are already in the target table so it'll need to return 20k rows to insert 10k additional rows into the target table. You expect to insert a total of 20 million rows. With a batch size of 10k rows you'll need to do 2000 batches. Each batch will on average need to read 10 million rows from the SELECT query, so for your process to complete you'll need the SELECT part of the INSERT to generate 20 billion rows. That's a lot of work and that work is unavoidable with the looping method that you're using here.

The other thing that immediately stands out is the use of the local variable @BatchSize without a RECOMPILE hint. You used that in a TOP statement and by default SQL server will assume that you want just 100 rows returned by the query. The query optimizer doesn't know what the value of the local variable is without a RECOMPILE or some other hint. The query optimizer can pick different plans based on the presence of a TOP operator, and with an estimated value of 100 it might pick a plan that's very inefficient for what you're trying to do. This is where examining and posting the estimated or actual query plan can really help.

Let's suppose that the query optimizer chooses to do a nested loop join against the plan2 table in the NOT EXISTS. That part of the query will do more work on each successive loop, but it's even worse than before. For the Nth loop you'll need to scan 10000 * (N - 1) rows 10000 * N times. If you have 270k rows already inserted into the table that means that N has a value of 28, so you could be scanning 10000 * 27 * 10000 * 28 = 75600000000 total rows just to insert the next 10k rows. That could easily take 7 hours to complete. You may be hitting this issue or you may be running into a completely different one. Getting rid of the @BatchSize variable or using a hint may help.

My approach to batched inserts is to avoid looping on the SELECT part if possible. Are you able to drop all nonclustered indexes on the target table before the insert? Is there a database with a recovery model of SIMPLE that you can write a temporary copy of the data to? Tempdb can work if there's enough space there. You may be able to take advantage of minimal logging to skip the looping. Probably all that's needed is the addition of a TABLOCK hint. If your current database has the right recovery model you may just be able to do one insert of 20 million rows. If not, you may be able to insert into another database and to loop over a temporary copy of that data to insert into your target table. The important thing is that if you need to loop make it so that each loop takes appropriately the same amount of time. You don't want the amount of work per loop to increase linearly or quadratically like it currently is.

If doing a single insert isn't an option you should change your style of looping. The goal is to avoid processing the same row more than once from the source tables. You also want to insert in clustered order of your target table if possible. I don't know your data model so I can't give very specific advice, but does the plan1 table have a primary key? Is there some kind of relationship between the number of rows in the plan1 table and the number of rows that you need to insert into plan2? As an example, you can try writing code that processes the plan1 table in 10k row chunks. With the right indexes that can be a relatively efficient query. I have a blog post that goes over a bunch of different methods of looping. The examples in the post just read data from a single table but you could adapt them to the SELECT query that you have. Just to stress this point again, you're looking for a strategy that does a constant amount of work per loop.

if you do need to keep the NOT EXISTS for some reason the following code is much more typical and I think that it would accomplish what you need:

WHERE NOT EXISTS (
    SELECT 1
    FROM plan2
    WHERE dr1.link = plan2.l_dr_plan1
    AND ad1.link = plan2.l_address
    AND dr.link = plan2.l_provider
)

It's not clear to me that the problem resides with the ltrim or rtrim, but it's always good to remove functions like that when you can.


Does large amout of idle connections hurt performance of Mysql We have applications that keep large amounts of idle connections (thoudsands). Does that hurt performance of the database? Is it worth it to lower the number idle connetions for performances sake?

Not much.
Why are there thousands?  Hundreds is reasonable.
Each connection takes some amount of RAM; if there are too many connections, you could run out of RAM, which would be bad for performance.
Also, if more than a few dozen connections suddenly get busy, they would be contending with each other for CPU, RAM, I/O, etc.
There is a "wait_timeout" that will disconnect a connection if it is idle for too long.


Running an "ORDER BY CASE" query on MySQL view breaks it afterwards Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable.

The newly created view works properly and all SQL is correct.

This is the error message I get when I try to view all records from the view (or when I click on "Browse" in phpMyAdmin):

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'THEN0 ELSE1 END LIMIT 0, 30' at line 1

Here is example schema:

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `comments` (`id`, `user_id`, `title`) VALUES
(1, 1, 'user one comment 1'),
(2, 1, 'user one comment 2'),
(3, 2, 'user two comment 1'),
(4, 2, 'user two comment 2');

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'user1'),
(2, 'user2');

Sample view:

CREATE VIEW view_sample AS
SELECT
  c.id AS comment_id,
  c.title AS comment_title,
  u.name AS user_name
FROM comments c, users u
WHERE c.user_id = u.id

At this point you can verify that the view above works properly and shows results.

Sample query that will break everything:

SELECT * FROM view_sample
WHERE comment_title LIKE 'user one%'
ORDER BY CASE WHEN comment_title LIKE 'user one%' THEN 1 ELSE 0 END, comment_title

Query above will work, but whatever you try to do afterwards will fail. Try simply this:

SELECT * FROM view_sample

I tried this several times, with different views.

What am I doing wrong?

1)Syntax error: need add space between THEN and 0 (eq. ELSE).


Is it possible to have more than one replica on a PostgreSQL server? version: PostgreSQL 11.4
OS: Windows 10

This is an illustration of my question:

On 3 different machines there are one primary server per machine.

Is it possible to have one common server where replicas of these primary servers are?

When configuring a replica, I am instructed to delete the current database (the PGDATA directory), if there is one. Thus my question. If I already have a replica on that database, it is then deleted?

Do I have to have several installations of PG? Having them on their own VM's?

You understood correctly. Since streaming replication keeps the replica a physical copy of the primary database cluster, you can only replicate between one primary and one replica.

There are two options:

Keep three clusters on the replica machine, one for each primary cluster.
Use logical replication.

With logical replication, you have to make sure that each subscription replicates to different tables, ideally in different databases.


Using EncryptByPassPhrase in where condition I am using EncryptByPassPhrase of sql server. But i am not able to use where condition properly.

DECLARE @login_details TABLE(uid integer,username varchar(10),password varbinary(100))
insert into @login_details(uid,username,password) values(1,'abc',EncryptByPassPhrase('12','XXX'))
insert into @login_details(uid,username,password) values(1,'nvt',EncryptByPassPhrase('12','YYY'))
select * from @login_details --this give all rows from table with encrypted values--

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details --this gives decrypted values

But in below query i am using where condition but it does not work and result in 0 rows.Why?

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where password=EncryptByPassPhrase('12','YYY')

where as below query works fine.

select uid,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password)) as password from @login_details
where convert(varchar(10), DECRYPTBYPASSPHRASE ('12',password))='YYY'

Why there is a problem if i include encryptbyphrase command in where condition?

You need to use the condition that works. The reason is easy to demonstrate. Run this:

DECLARE @x VARBINARY(8000) = ENCRYPTBYPASSPHRASE('12','YYY');
SELECT @x, CONVERT(VARCHAR(32), DECRYPTBYPASSPHRASE('12', @x));
GO 5

Do you see the same result every time? No. This is because the encryption adds some magic to prevent predictable results (that is grossly over-simplified, but for the purpose of this question, this is kind of how 3DES works to produce this symptom).

So, the way to do this is to decrypt first, then compare.

Of course, this kills sargability on the password column (if it is indexed), forcing a full scan (if this is really the only predicate). If you need efficient seeks on this column (seems unlikely that you'd ever just be checking for the password to match), you might need to consider a "locked-down" computed/persisted/indexed column.

As an aside, funny that a Google search for encryptbypassphrase where clause has hit #1 as a Stack Overflow answer by me.


SQL Server 2016 Express restore size limit SQL Server 2016 Express indicates that it will accept a database of 10GB. I try to do a restore with overwrite of existing blank database with a 8.037GB .bak file and get the message that the file is too big to restore. Any suggestions?

SQL Server leaves out empty space when it backs up a database. If you want to know the size of the actual data and log files inside the backup file, you need to view the header of the file.

From inside Management Studio, run the following query (you should fill in the correct value to the path of the backup file):

RESTORE FILELISTONLY FROM DISK = 'C:\path\to\backup.bak';

The results will show one or more data files, and one transaction log file.

Add up the values in the Size column to find out exactly how big the database is. The total cannot exceed 10GB.


convert legacy showplan text into graphical format Does anyone know of a way to convert legacy showplan text into a graphical format?

In other words, can I take the StmtText returned from SET SHOWPLAN_TEXT ON and convert that to show me a graphical execution plan? Would I need to parse it into XML, and the .sqlplan format in order to see the graph?

Eric:, here is the example, run against AdventureWorks:

SET SHOWPLAN_TEXT ON
GO

SELECT c.CustomerID, soh.ShipDate
FROM Sales.SalesOrderDetail sod
    INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
    INNER JOIN Sales.Customer c ON c.CustomerID = soh.CustomerID
WHERE sod.SpecialOfferID = 2
AND soh.ShipDate between '2003-01-01 00:00:00.000' and '2004-01-01 00:00:00.000'
AND c.CustomerType = 'I'

And you will see this StmtText returned:

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Hash Match(Inner Join, HASH:([soh].[CustomerID])=([c].[CustomerID]))
       |--Hash Match(Inner Join, HASH:([sod].[SalesOrderID])=([soh].[SalesOrderID]))
       |    |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[ix_nc_SpecialOfferID_inc_SalesOrderID] AS [sod]), SEEK:([sod].[SpecialOfferID]=(2)) ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[ix_nc_ShipDate_inc_SalesOrderID_CustomerID] AS [soh]), SEEK:([soh].[ShipDate] >= '2003-01-01 00:00:00.000' AND [soh].[ShipDate] <= '2004-01-01 00:00:00.000') ORDERED FORWARD)
       |--Index Seek(OBJECT:([AdventureWorks].[Sales].[Customer].[ix_nc_CustomerType] AS [c]), SEEK:([c].[CustomerType]=N'I') ORDERED FORWARD)

I have been told by colleagues at Microsoft that this is not possible, as the legacy SHOWPLAN_TEXT does not have enough details to display in a graphical format.


Log Shipping Disk Sizes Do the drives that contain the database have to be the exact same size on the primary server as secondary?
I currently have a primary database for OLTP and a standby database used for reporting.
I'm going to create 2 new standby databases then cut over to them as an Always On Availability Group.
The 2 AG databases must be exact matches but can't the new standby disks be different size than the current primary - as long as the drive letters match and the files fit?
Thank You!

You're confusing some technologies here, unless I'm missing something or misinterpreted.  Log shipping is FAR different than Availability Groups.

Your drives on the secondary server do not have to be the exact same size, they simply need to have enough room for the DBs.  That being said, it's usually a good idea to size things the same across environments that are being replicated.  What if your primary DB grows and you have enough room on the primary but not enough on the secondary?  That will take your replication down and cause things to break.


What is the fastest way to reset SQL Server database if you can not use RESTORE? I have integration tests that require test database to function.
Since tests should generally be independent, I reset the database in the beginning of each
test.

I can not use RESTORE, since some parts of architecture (which I do not control) are caching the connections, and will fail with connection lost on next call.

Right now I am creating a snapshot, then calling DELETE + INSERT on each table to synchronize data with snapshot. However, it takes 1 second per reset which is way too much (150 tests = 150 seconds). I have a lot of tables, but they are almost empty so there is no reason for it to be so slow.

So how can I replace database with its previous version in less than 1 second without losing connections?

My next idea would be to add some kind of change tracking since each test only affects some tables, but it would make reset code even more complicated.

UPDATE: I added SET STATISTICS TIME ON and I am getting

SQL Server parse and compile time:
   CPU time = 327 ms, elapsed time = 343 ms.

for my reset SP. I assume it is due to ALTER TABLE ... NOCHECK CONSTRAINT ALL calls in the beginning of the SP. I wonder if it is possible to suppress recompilation in this case.

Run each of your tests inside of a single transaction... at the end of each test, ROLLBACK the transaction.


how much free space do I need for db2 backup file? I have a db2 database on Linux (DB2 10.5 fp08).
Now I would like to backup it, before backup I can get the database size with db2 "call GET_DBSIZE_INFO (?,?,?,0)"
For example it is 6 GB.

I know if the backup location has >6 GB free space is Ok for this backup.

But do you know if there is any offical documents/post about this.
What is the best higher free space for this kind of backup?

The backup image contains only used extents, so it might be substantially smaller than the sum of tablespace sizes, depending on the tablespaces' types and their physical properties.

On the other hand, in DB2 10.5 the backup image will by default include log files that are modified during the backup, so depending on how active the database is during the backup you may need to include the log sizes into your calculation.

Finally, the backup image can be compressed. Compression ratio is impossible to estimate in advance.

As a result, you can only estimate the upper limit for free space required to store the backup image, which would be the sum total of the current tablespace sizes plus the size of included log files plus the backup image header.

See also this answer on Stackoverflow.


xampp mysql slow after bind 0.0.0.0 Need to access xampp mysql database remotely for replication but when I do bind 0.0.0.0 it just slows the database down completely, can anyone shed some light?

thanks :)

Fixed by connecting the database via 127.0.0.1 rather than localhost, thanks all!


Does a simple select query acquire locks? I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks.

Select * from Student;

Please consider the case where the statement would not be running inside a begin tran block.

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.


Can SQL Server 2016 log files be applied on SQL Server 2012 This is in relation to a rollback scenario from a 2012 -> 2016 upgrade.

The scenario is that the SQL Server 2012 database is restored onto 2016 and log files applied until it is up to date. Then production systems are cut over to 2016.  Database is kept at compatibility level for 2012.  Subsequently, incremental logs from the 2016 instance are kept until the need to roll back (let's say 8 hours worth).

Can the logs now be applied to the SQL Server 2012 instance to bring that up to date?

No we won't be able to do that. This is because the internal file structure is different.

You can use third party tools like SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.

I hope this helps


Misunderstanding statement in MySQL documentation I can't understand this statement in Optimizing Data Size:

  Declare columns with identical information in different tables with
  identical data types, to speed up joins based on the corresponding
  columns.

Can anyone describe this statement with example?

If you need to JOIN or compare columns, then ensure they are the same datatype. This avoids implicit conversions which usually invalidate index usage.

However, for JOINs especially this can be completely prevented by defining a foreign key constraint: this forces you to have the same datatype. See MySQL docs which says:

  Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same


SQL server Import CSV file - 1'st row is also added as s column name I'm trying to import a CSV file using import export wizard, Im facing an issue.
The first row is also added as a column name.

Import wizard:

The highlighted area is my 1'st row.

I used your data and was able to do it without any issues. Seems your first row of data does not have {CR}{LF}. The snap of data looks like there is an auto new line rather than you providing it.And that is why the 3rd column has ' Is_Active""1, so the wizard is looking for a comma now. Below is my test with data of similar type :


Is best practice to Web Application users credentials using same database users credentials I have multiple Web applications using same database (MariaDB), so i want to using same user credentials as centralize, so:

Is it best practice to using same database users credentials and
when add new user add the user also on the database with proper
privilege ??
OR
create separate table for the users and the any transaction using
only one database user to connect, insert, delete, update, ....
Thanks & Regards.

It is certainly possible to use database usernames to authenticate web application users, but that is rather uncommon.
End users are typically authenticated at the enterprise level - meaning they use the same identity in a single-sign-on fashion to identify themselves with multiple enterprise applications.
Then in web applications, those identities are mapped to a small number of specific database users with capabilities matching the roles/profiles of the end users.
Having all enterprise users (and much less end users) defined in all databases becomes a logistic nightmare once the number exceeds a few ten's of users. It is definitely not possible once you start having end-users/customers accounts going rapidly into the millions.


MySQL: Is it possible to order a query by a specific letter using ORDER BY? What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ORDER BY? I know I can use WHERE field LIKE 'D%', but I'd like all the results not just those with D.

My query currently looks as follows:

SELECT pname, pdescription, price FROM products WHERE manufacturer = 3 ORDER BY pname ASC

Have a look at below example

DROP TABLE IF EXISTS products;

create table products(pname CHAR(30),pdescription CHAR(30),price DECIMAL(10,2),manufacturer CHAR(30));

INSERT INTO products VALUES
    ('Toys','These are toys',15.25,'ABC'),
    ('Dolls','These are Dolls',35.25,'PQR'),
    ('DustPan','These are DustPan',75.25,'AZD'),
    ('Doors','These are Doors',175.25,'RAZD'),
    ('TV','These are TV',11175.25,'RAZD'),
    ('Bed','These are Bed',1175.25,'ARAZD');

/** Check all data **/

SELECT * FROM products;
+---------+-------------------+----------+--------------+
| pname   | pdescription      | price    | manufacturer |
+---------+-------------------+----------+--------------+
| Toys    | These are toys    |    15.25 | ABC          |
| Dolls   | These are Dolls   |    35.25 | PQR          |
| DustPan | These are DustPan |    75.25 | AZD          |
| Doors   | These are Doors   |   175.25 | RAZD         |
| TV      | These are TV      | 11175.25 | RAZD         |
| Bed     | These are Bed     |  1175.25 | ARAZD        |
+---------+-------------------+----------+--------------+
6 rows in set (0.00 sec)

/** Order by D% **/
SELECT
        pname, pdescription, price
    FROM
        products
    ORDER BY
    CASE
        WHEN pname LIKE 'D%' THEN 1
        ELSE 2
    END;
+---------+-------------------+----------+
| pname   | pdescription      | price    |
+---------+-------------------+----------+
| Dolls   | These are Dolls   |    35.25 |
| DustPan | These are DustPan |    75.25 |
| Doors   | These are Doors   |   175.25 |
| Toys    | These are toys    |    15.25 |
| TV      | These are TV      | 11175.25 |
| Bed     | These are Bed     |  1175.25 |
+---------+-------------------+----------+
6 rows in set (0.00 sec)


Rewrite trigger from PostgreSQL to MySQL I have this trigger in PostgreSQL database:

CREATE FUNCTION connections_insert_update_connection_root_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                UPDATE connections SET connection_root = connection WHERE connection = NEW.connection AND NEW.connection_root IS NULL;
                RETURN NEW;
            END;
            $$;

How it will look in MySQL please?

The table connections in MySQL looks this:

| connections | CREATE TABLE `connections` (
  `connection` int(11) NOT NULL AUTO_INCREMENT,
  `connection_type` text,
  `connection_transport` text,
  `connection_protocol` text,
  `connection_timestamp` int(11) DEFAULT NULL,
  `connection_root` int(11) DEFAULT NULL,
  `connection_parent` int(11) DEFAULT NULL,
  `local_host` text,
  `local_port` int(11) DEFAULT NULL,
  `remote_host` text,
  `remote_hostname` text,
  `remote_port` int(11) DEFAULT NULL,
  PRIMARY KEY (`connection`),
  KEY `local_port` (`local_port`),
  KEY `connection_parent` (`connection_parent`),
  KEY `connection_root` (`connection_root`),
  KEY `connection_timestamp` (`connection_timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

I tried this. It is semantic correct please?

CREATE TRIGGER connections_INSERT_update_connection_root_trg AFTER INSERT ON connections
FOR EACH ROW
BEGIN
    IF connection = NEW.connection AND NEW.connection_root IS NULL THEN
    UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
    END IF;
END$$

Thank you for your help.

You cannot do this in an AFTER INSERT trigger. Why ???

According to the Book

Chapter 19 Page 251 Paragraph 3 under the subheading BEFORE and AFTER TRIGGERS

  The most significant difference between BEFORE and AFTER triggers is that in an AFTER trigger you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late change what the DML is going to do.

Just make this as a BEFORE INSERT trigger and update the needed columns:

DELIMITER $$
CREATE TRIGGER connections_INSERT_update_connection_root_trg BEFORE INSERT ON connections
FOR EACH ROW
BEGIN
    IF NEW.connection_root IS NULL THEN
        NEW.connection_root = NEW.connection;
    END IF;
END $$
DELIMITER ;

GIVE IT A TRY !!!


Auditing user queries of specific rows in a table Is it possible in SQL Server 2017 and upwards to audit the access to certain rows in a table? Imagine a customer table, and we wanted to track when users selected the data for premium customers identified by their primary key; or maybe employees querying the salary information of their fellow employees (but not too worried about them querying their own salary information).

With the wealth of auditing and security technologies that Microsoft are throwing at SQL Server these days, is there anything among e.g. extended events, row level security, auditing etc that would help me?

Wouldn't it make more sense to set the permissions so they can only access the things they are authorised to rather than audit and log that they did something unauthorised - that said:-

I think if you built 2 views which filtered the base tables to "sensitive" and "normal" rows and only permitted access via the views you could then audit the views.


How to create stored procedure that returns query result? I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There are situations when I need to pull whole table result set and sometimes I need to look for specific record only (filtered by id). Here is example of the query:

SELECT
    TRIM(t1.rec_id) AS rec_id,
    TRIM(t1.date) AS date,
    TRIM(t1.name) AS name,
    TRIM(t1.address) AS address,
    TRIM(t2.year) AS year,
    CASE
        WHEN LENGTH(TRIM(t3.percent)) <> 0 THEN CONCAT( ROUND( TRIM(t3.percent) * 100, 2) ,'% Whole' )
        WHEN LENGTH(TRIM(t3.partial)) <> 0 THEN CONCAT( ROUND( TRIM(t3.partial) * 100, 2) ,'% Partial' )
    END AS percent
FROM table1 t1
    LEFT OUTER JOIN table2 t2
        ON TRIM(t1.rec_id) = TRIM(t2.rec_id)
    LEFT OUTER JOIN table3 t3
        ON TRIM(t1.rec_id) = TRIM(t3.rec_id)

-- Here I would like to add WHERE clause filter
-- If RecID argument is not NULL and is valid then query should filter
-- and return only one record. If not return all records.

ORDER BY t1.rec_id

Query above usually returns around 500-600 records. It's the main source of my application. In some situations I need all records and sometimes only specific record. So in my comment inside of the code above I explained the situation. The stored procedure should have one argument RecID. If that argument is NULL then stored procedure should return all records and columns. If ti's not NULL then it should look for specific RecID and return that row with all columns. I have done some work in SQL 2008+ where I used stored procedures. I'm wondering if someone can show me an example on how this would work in Oracle? I use Oracle 11g with ColdFusion 10. If someone have good example on how this can be completed please let me know. Thank you!

As of a view I suggested in a comment: as you already have a SELECT statement, it is a matter of a few letters to convert it to a view. Here's an example:

SQL> select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  2  from dept d join emp e on e.deptno = d.deptno;

    DEPTNO DNAME               EMPNO ENAME      JOB              SAL
---------- -------------- ---------- ---------- --------- ----------
        10 ACCOUNTING           7839 KING       PRESIDENT       5000
        10 ACCOUNTING           7782 CLARK      MANAGER         2450
        10 ACCOUNTING           7934 MILLER     CLERK           1300
        20 RESEARCH             7902 FORD       ANALYST         3000
        20 RESEARCH             7369 SMITH      CLERK            800
        20 RESEARCH             7566 JONES      MANAGER         2975
        30 SALES                7900 JAMES      CLERK            950
        30 SALES                7844 TURNER     SALESMAN        1500
        30 SALES                7654 MARTIN     SALESMAN        1250
        30 SALES                7521 WARD       SALESMAN        1250
        30 SALES                7499 ALLEN      SALESMAN        1600
        30 SALES                7698 BLAKE      MANAGER         2850

12 rows selected.

SQL>

Convert it to a view:

SQL> create or replace view v_emps as
  2  select d.deptno, d.dname, e.empno, e.ename, e.job, e.sal
  3  from dept d join emp e on e.deptno = d.deptno;

View created.

SQL>

See? Lines 2 and 3 are what you already have.

Now, when you want to select something, you'd use a view instead of the same SELECT statement over and over again. For example:

SQL> select dname, sum(sal) from v_emps group by dname;

DNAME            SUM(SAL)
-------------- ----------
ACCOUNTING           8750
RESEARCH             6775
SALES                9400

SQL> select job, count(*) from v_emps group by job;

JOB         COUNT(*)
--------- ----------
CLERK              3
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            1

SQL> select ename from v_emps where deptno = 20;

ENAME
----------
SMITH
JONES
FORD

SQL>

Quite simple, isn't it?

A view is nothing but a stored query - it doesn't occupy any space, it always returns current data. True - if it is complex and deals with A LOT of data, it may be slow; but then performance tuning takes place.

I'd suggest you to try it; won't cost much, as you've seen - just a simple create or replace view v_emps ...


Best block/stripe size for log volume? I know the common recommendation for the data volume in SQL Server is to use 64 KB blocks/stripes, since the I/O is typically done by entire extents. I can't find any good information regarding log file I/O, however.

I've been watching I/O activity in Process Monitor for a little while, and it appears that the log file I/O sizes range from 512 bytes to just under 64 KB. I'm guessing this is dependent on the size of the transaction being logged, and large ones get split up using multiple ~64 KB writes.

So, assuming I have the partition aligned to the RAID stripes, would it be safe to assume that 64 KB blocks/stripes will yield the best performance, all other things being equal? I would expect that the smaller transactions, i.e. the ones with 512-byte writes, aren't coming heavily enough for the large block size penalty to have a significant impact, whereas the much larger transactions writing lots of 64 KB blocks in rapid succession would be more important to tune for.

64k blocks are usually best.  Log writes should actually max out at about 60k, but at 64k you are aligned with the size of the physical block on the disk.


Should login id and password be stored in separate table I am working on my academic project. I have a table for employee records which have employee id, joining date, designation and other fields. I am trying to implement a scheme in which when record of employee is added first time in DB, a user name and temporary password will be generated so that New employee can use it to login in DB to see his details.
I need to ask if it is good practice to save login id and password in employee record table? Or should I store them separately, but then I think I can not generate temporary password and id at time of inserting employee data in DB.
Or is there any other way to implement this scheme?

Edit: I understand that I should never save clear text password in DB and should use salt/hash. However I am looking for a way that above task can be done in one action, i.e If I make a separate table for user id and password then how can I am able to enter employee details in employee table and generate user id / password for user and store in from separate table in one action.

Whether you use one table or two, you should be able to insert all the data needed for in a single transaction.

More importantly, you should not store the password, but instead store a password hash. Here is one post on using a password hash such as SQL Server itself uses.  This is using SHA_512 hashing with a 32-bit salt.

http://sqlity.net/en/2460/sql-password-hash/

This way you never store the actual password. At the time of the creation of the password, you need to share the password with your new user. And ideally force him to change the password at first use.

Each time a new password is created, you must, of course, update the hash to match the current password. Which means that you must manage all password changes, not just the initial password.

EDIT Re: "how can I am able to enter employee details in employee table and generate user id / password for user and store in from separate table in one action."

You must do the work in a transaction.  Here is a sketchy outline using 3 stored procedures.  Of course, you need to write the actual code:

BEGIN TRANSACTION;
   EXEC InsertNewUser @Name='UserName', ... , @NewUserId INT OUTPUT;
   EXEC CreateNewUserPassword @UserID=@NewUserID, ..., @Password NVARCHAR(128) OUTPUT;
   EXEC SendPasswordToNewUser @NewUserID, @Password;
COMMIT TRANSACTION;

By collecting the new @UserID and @Password values you can use them in the following steps.


How to set query_cache_type=2 on mac? I want to enable query_cache_type and change its value to DEMAND.
When I am doing it via terminal its giving this error:

  ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

The path for my.cnf is /usr/local/mysql/my.cnf.

Could not find proper my.cnf, so had to find the preferred location where mysql is searching for my.cnf by using the command:

mysql --help | grep cnf

which displays

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

So I created a file  /etc/my.cnf with the required settings, restarted the server, and everything worked like a charm.


i have error in query i am not getting what is the issue in the query probably i am not following the correct way to put the string and char sign , i am inserting the data in c# to local host with where clause please check the query and Error i am getting

String insertQuery = "insert into exam_add (id,session_id,Title,From_date,To_date,class_id,is_Post,is_Lock) select '"+id+ ",s.session,'" + title.Text+",'"+ from.Value.Date.ToString("yyyy-MM-dd")+",'"+to.Value.Date.ToString("yyyy-MM-dd")+ ", c.class_name,'"+x+",'"+x+" from year_session s, classes c where s.id = '1' and c.id='" + cls + "'";

Error

If you add static strings to your SELECT Statement you always have to surround them on both sides with single ticks.

But you should urgently consider prepared statements like
https://docs.microsoft.com/de-de/dotnet/api/system.data.sqlclient.sqlcommand.prepare?view=netframework-4.8

String insertQuery = "insert into exam_add
  (id,session_id,Title,From_date,To_date,class_id,is_Post,is_Lock)
select
   '"+id+ "'
   ,s.session
   ,'" + title.Text+"'
   ,'"+ from.Value.Date.ToString("yyyy-MM-dd")+"'
   ,'"+to.Value.Date.ToString("yyyy-MM-dd")+ "'
   , c.class_name
   ,'"+x+"'
   ,'"+x+"'
 from year_session s, classes c
 where s.id = '1' and c.id='" + cls + "'";


how to select first 9 numbers from other table from database using sql query? I want to fetch first 9 numbers from matching with other column please help me to find out thank you....?

SELECT MAINFILE.facno,GLMAST.fglhead
FROM MAINFILE
INNER JOIN GLMAST ON LEFT(MAINFILE.facno,9) like GLMAST.fglcode and GLMAST.fglhead='" & ComboBox2.Text & "' "

I am matching glcode='010000101' with facno='010000101000001' where i want to select first 9 numbers to match with glcode from facno...i hope my concept understood..

If I understand correctly your requirement, this should do the trick:

WITH MAINFILE (facno) AS (
    SELECT '010000101000001'
),
GLMAST (fglcode) AS (
    SELECT '010000101'
)
SELECT MAINFILE.facno --,GLMAST.fglhead
FROM MAINFILE
INNER JOIN GLMAST
    ON MAINFILE.facno LIKE GLMAST.fglcode + '%'
    --and GLMAST.fglhead='" & ComboBox2.Text & "' "

Please note that the predicate on GLMAST.fglhead is prone to SQL injection. I strongly suggest to rewrite this code in a more secure way.


How to Pivot Multiple Columns I have the following data,

Year        DRC             DISP        Id
0           140.21          0.00        5808
1           112.37          1.00        5808
0           140.21          0.00        5824
1           112.37          0.00        5824

I want to pivot it like this,

Id          DRC Year 0      DISP Year 0     DRC Year 1      DISP Year 1
5808        140.21          0.00            112.37          1.00
5824        140.21          0.00            112.37          0.00

I have tried this,

SELECT *
FROM
(
    SELECT Year, DRC, DISP, ID From OriginalDataTable
) AS SourceTable

PIVOT
(
    max(DRC)
    for Year IN ([0], [1])
) AS PivotTable;

It pivots on the DRC, but not on DISP as well. The result is like this (0, and 1 is the DRC value). I understand why that is, but how do I pivot on the DISP as well?

DISP        Id          0           1
0.0         5808        140.21      112.37
0.0         5824        140.21      112.37

Instead of the PIVOT you can simply use the CASE WHEN .... By using GROUP BY then use MAX(CASE will return your expected result:

Sample execution with the given sample data:

DECLARE @OriginalDataTable TABLE (
    [Year] INT, DRC DECIMAL(18,2), DISP DECIMAL(18,2), Id INT);

INSERT INTO @OriginalDataTable ([Year], DRC, DISP, Id) VALUES
(0, 140.21, 0.00, 5808),
(1, 112.37, 1.00, 5808),
(0, 140.21, 0.00, 5824),
(1, 112.37, 0.00, 5824);

SELECT [Id],
       MAX(CASE WHEN [Year] = 0 THEN DRC END)  AS [DRC Year 0],
       MAX(CASE WHEN [Year] = 0 THEN DISP END) AS [DISP Year 0],
       MAX(CASE WHEN [Year] = 1 THEN DRC END)  AS [DRC Year 1],
       MAX(CASE WHEN [Year] = 1 THEN DISP END) AS [DISP Year 1]
FROM @OriginalDataTable
GROUP BY [Id]

Output:

Id      DRC Year 0   DISP Year 0   DRC Year 1   DISP Year 1
5808    140.21       0.00          112.37       1.00
5824    140.21       0.00          112.37       0.00

More explanation:

SELECT [Id],
       CASE WHEN [Year] = 0 THEN DRC END   AS [DRC Year 0],
       CASE WHEN [Year] = 0 THEN DISP END  AS [DISP Year 0],
       CASE WHEN [Year] = 1 THEN DRC END   AS [DRC Year 1],
       CASE WHEN [Year] = 1 THEN DISP END  AS [DISP Year 1]
FROM @OriginalDataTable

The above query returns the result below:

Id      DRC Year 0  DISP Year 0  DRC Year 1  DISP Year 1
5808    140.21      0.00         NULL        NULL
5808    NULL        NULL         112.37      1.00
5824    140.21      0.00         NULL        NULL
5824    NULL        NULL         112.37      0.00

To avoid the NULL values and the Id wise result, I used GROUP BY Id then MAX() of the each column, so in the final result we can get the not NULL values as result.


How to insert data into tables which reference each other?(MySQL) I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet).
I'm not going to give my «homework» because I want to learn what I'm doing but I will use an example.
I have the following diagram:

And the following:
Employee(ID,DepartamentID,Floor)
foreign key(DepartamentID, Floor) references Departament(DepartamentID,Floor)
Departament(DepartamentID,Floor, Attendant)
foreign key Attendant references Employee(ID)

So with this guidelines, my actual SQL code is:
CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
    CONSTRAINT pk_ID PRIMARY KEY (ID),
) ENGINE=InnoDB;

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
    Attendant varchar(25) not null,
    CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
    CONSTRAINT fk_att FOREIGN KEY (Attendant) REFERENCES Employee (ID),
) ENGINE=InnoDB;

ALTER TABLE Employee
ADD CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor) REFERENCES Departament (DepartamentID, Floor);

I think, this way, I accomplish what I have to accomplish as the information given says, but when I try to insert data like:
INSERT INTO Employee (ID, DepartamentID,Floor) VALUES ('123456789-Z', 'IT', 'roof');

or
INSERT INTO Departament (DepartamentID, Floor, Attendant) VALUES ('IT', 'roof', '123456789-Z');

I get the following message:
1452 - Cannot add or update a child row: a foreign key constraint fails
After searching and searching, I realized that is not possible to insert data into a table that expects data from another table and that another table is empty.
I thought that I could do the alter table before the inserts but the teacher wants a create.sql file and a inserts.sql file, separately, so I cannot do it this way.
I need to find a solution to accomplish this and stackexchange is my last hope.
Thanks to anyone that read this, thank you very much.
PD: Sorry if the question is too long, I tried to explain it the best I can(english is not my native language) but if I have to explain something again, sure I will!

Not sure I understood the task, but here is some food for thought for an alternative schema:

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
        CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
) ENGINE=InnoDB;

CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
        CONSTRAINT pk_ID PRIMARY KEY (ID),
        CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor)
            REFERENCES Departament (DepartamentID, Floor)
) ENGINE=InnoDB;

CREATE TABLE Attendant (
    ID varchar(25) not null,
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,

    CONSTRAINT pk_attendent PRIMARY KEY (ID),
    CONSTRAINT ak_attendent UNIQUE (DepartamentID, Floor),
    CONSTRAINT fk_... REFERENCES Employee ...,
    CONSTRAINT fk_... REFERENCES Department ...,
) ENGINE=InnoDB;


SQL Server 2008 - Partitioning and Clustered Indexes So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.

Comments about how we should rethink aspects of the design are likely correct but unhelpful :)

I have a very large table, approx 150 fields wide and about 600m rows,  that drives a large number of processes.  This is in a data warehouse situation so we don't have ANY updates/inserts outside the scheduled load process, so it is heavily indexed.

A decision has been made to try partitioning this table, and I have some concerns about indexing a partitioned table.  I don't have any experience with partitioning, so any input or links are appreciated.  I couldn't locate specifically what I am after on BOL or msdn.

Currently we cluster on a field that we'll call IncidentKey which is a varchar(50) and not unique - we could have between 1-100 records with the same IK (no comments please).  We do often get new data on old IncidentKey records so it's not sequential either.

I understand I need to include my partition field, IncidentDate, in my clustered index key for the partition to work correctly.  I'm thinking it would be IncidentKey, IncidentDate.

The question is, how will the mechanics of a clustered index work on a 2 part key in a partitioned table, if a record in a "new" partition should be before a record in an "old" partition in the clustered index?

For example, I have 5 records:

IncidentKey    Date

ABC123        1/1/2010
ABC123        7/1/2010
ABC123        1/1/2011
XYZ999        1/1/2010
XYZ999        7/1/2010

If I get a new record for ABC123, 2/1/2011 it will need to be in the clustered index BEFORE XYZ999, 1/1/2010.  How does this work?

I'm assuming fragmentation and pointers, but I can't find any info on the physical storage and configuration of non-partitioned clustered indexes on partitioned  tables with dual-part keys.

A partitioned table is really more like a collection of individual tables stitched together. So your in example of clustering by IncidentKey and partition by IncidentDate, say that the partitioning function splits the tables into two partitions so that 1/1/2010 is in partition 1 and 7/1/2010 is partition two. The data will be layed out on disk as:

Partition 1:
IncidentKey    Date
ABC123        1/1/2010
ABC123        1/1/2011
XYZ999        1/1/2010

Partition 2:
IncidentKey    Date
ABC123        7/1/2010
XYZ999        7/1/2010

At a low level there really are two, distinct rowsets. Is the query processor that gives the illusion of a single table by creating plans that seek, scan and update all rowsets together, as one.

Any row in any non-clustered index will have have the clustered index key to which it corresponds, say ABC123,7/1/2010. Since the clustered index key always contains the partitioning key column, the engine will always know in what partition (rowset) of the clustered index to search for this value (in this case, in partition 2).

Now whenever you're dealing with partitioning you must consider if your NC indexes will be aligned (NC index is partitioned exactly the same as the clustered index) or non-aligned (NC index is non-partitioned, or partitioned differently from clustered index). Non-aligned indexes are more flexible, but they have some drawbacks:

non-aligned indexes require large amounts of memory for certain query plans
non-aligned indexes prevent efficient partition switch operations

Using aligned indexes solves these issues, but brings its own set of problems, because this physical, storage design, option ripples into the data model:

aligned indexes mean unique constrains can no longer be created/enforced (except for the partitioning column)
all foreign keys referencing the partitioned table must include the partitioning key in the relation (since the partitioning key is, due to alignment, in every index), and this in turn requires that all tables referencing the partitioned table contain partitioning key column value. Think Orders->OrderDetails, if Orders have OrderID but is partitioned by OrderDate, then OrderDetails must contain not only OrderID, but also OrderDate, in order to properly declare the foreign key constraint.

These effects I found seldom called out at the beginning of a project that deploys partitioning, but they exists and have serious consequences.

If you think aligned indexes are a rare or extreme case, then consider this: in many cases the cornerstone of ETL and partitioning solutions is the fast switch in of staging tables. Switch in operations require aligned indexes.

Oh, one more thing: all my argument about foreign keys and the ripple effect of adding the partitioning column value to other tables applies equally to joins.