When `nvarchar/nchar` is going to be used with SQL Server 2019?

UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on the basis of actual requirements for comparison, sorting, data import, and export.

You may need to change more than you think, since e.g. an nchar(1) type provides two bytes of storage. That is enough to store any character in BMP (code points 000000 to 00FFFF). Some of the characters in that range would be encoded with just 1 byte in UTF-8 while others would require 2 or even 3 bytes (see this comparison chart for more details). Therefore, ensuring coverage of the same set of characters in UTF-8 would require char(3).

For example:

DECLARE @T AS table
(
    n integer PRIMARY KEY,
    UTF16 nchar(1) COLLATE Latin1_General_CI_AS,
    UTF8 char(1) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

INSERT @T (n, UTF16, UTF8)
SELECT 911, NCHAR(911), NCHAR(911);

gives the familiar error:

  Msg 8152, Level 16, State 30, Line xxx
  String or binary data would be truncated.

Or if trace flag 460 is active:

  Msg 2628, Level 16, State 1, Line xxx
  String or binary data would be truncated in table '@T', column 'UTF8'. Truncated value: ' '.

Expanding the UTF8 column to char(2) or varchar(2) resolves the error for NCHAR(911):

DECLARE @T AS table
(
    n integer PRIMARY KEY,
    UTF16 nchar(1) COLLATE Latin1_General_CI_AS,
    UTF8 varchar(2) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

INSERT @T (n, UTF16, UTF8)
SELECT 911, NCHAR(911), NCHAR(911);

However, if it was e.g. NCHAR(8364), you would need to expand the column further, to char(3) or varchar(3).

Note also that the UTF-8 collations all use supplementary characters, so will not work with replication.

Aside from anything else, UTF-8 support is only in preview at this time, so not available for production use.


Which first: upgrade mysql version or convert storage engine?

Of the choices, I would update, then convert.

InnoDB received some massive performance boosts in 5.5, so by upgrading first you get some features that 5.5 introduces, and then you can convert to InnoDB to get full innodb benefit.

5.0 InnoDB performance was not great, so you might see some strange issues by converting to InnoDB prior to updating to 5.5

As always though, read through the MySQL 5.5 upgrade notes to make sure there's nothing new that would break your current implementation.

And, of course, test it out on a non-production database first.


Prologue

This is enhancing my answer from your other question:

Enforce uniqueness across multiple rows

pre-19c - easy method

Calculate the hash-value of your list of activities and look it up in the group_activities#lock table.

pre-19c - dark side

Create an OPERATOR that does the comparison between two activity_list_nt values.

I consider this a "not ideal solution" because it is tempting you down the path of building your own Domain Index.

19c+

19c introduces the JSON_EQUAL operator.  This makes list comparison easy.

with group_activities_dml as (
    select group_id, json_arrayagg( activity_id ) activity_list_json
    from groups
    group by group_id
)
select *
from group_activities_dml
where json_equal( activity_list_json, '[1,2]' )


sql server Group by with calculated values

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once.

When do you want the validity_name to equal 1?

Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column.

Using AdventureWorks as an example, this works like so:

USE AdventureWorks;
GO

SELECT
  FirstName,
  ValidName
FROM Person.Person AS p
CROSS APPLY (VALUES(CASE WHEN FirstName = 'Kim' THEN 1 ELSE 0 END)) AS a(ValidName)
GROUP BY FirstName, ValidName

When applied to your query, it should be something like the following (untested, use the example above to build your query):

select
       p.id,
       p.name,
       p.surname,
       p.adress,
       a.validity_name,
       b.validity_surname,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id)
        CROSS APPLY (VALUES(case when(p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 END)) AS a(validity_name)
        CROSS APPLY (VALUES(CASE WHEN p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%') then 0 else 1 END)) as b(validity_surname)
group by id, name, validity_name, surname, validity_surname, adress


Are "create user" and "grant connect" functionally equivalent?

You can't grant database permissions to server principals: you need to create a database principal first, which is what you probably have already done.

What you are referring to is the lack of CONNECT privileges to the GUEST principal, which was likely revoked by a wise DBA. When CONNECT privileges is not granted explicitly to a database principal, it inherits the permission from the guest user.

Here is a repro script that can help you understand how things work:

-- create a test database
CREATE DATABASE testPermissions;
GO

USE testPermissions;
GO

-- revoke CONNECT permissions from users that
-- are not granted CONNECT explicitly or
-- do not inherit the permission from server
-- or database roles.
-- this is considered a security best practice
REVOKE CONNECT FROM GUEST;
GO

-- create a test table
CREATE TABLE someTable (
    someColumn int
);
GO

-- insert some values
INSERT INTO someTable VALUES (1);
GO

-- create a login, AKA server principal
-- this login has NO PERMISSIONS on the database
CREATE LOGIN testlogin WITH PASSWORD=N'aVeryLongPasswordNobodyWillEverGuess', CHECK_POLICY = OFF
GO

-- if you try to grant database permissions
-- to a login you get an error
GRANT SELECT TO testlogin;
GO

-- Msg 15151, Level 16, State 1, Line 1
-- Cannot find the user 'testlogin', because it does not exist or you do not have permission.

-- if you create a database user you are creating
-- a link between a database principal and
-- a server principal with the "FOR LOGIN" clause
CREATE USER testUser FOR LOGIN testLogin;
GO

-- now if you grant some permissions to the
-- database principal you won't get any errors
GRANT SELECT TO testUser;
GO

-- you can now see that the database principal
-- has been granted some permissions
SELECT pe.class_desc
    ,OBJECT_NAME(pe.major_id) AS target_object_name
    ,pe.permission_name
    ,pr.name AS grantee
    ,pr.type_desc
FROM sys.database_permissions AS pe
LEFT JOIN sys.database_principals AS pr
    ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name = 'testUser';
GO

-- class_desc  target_object_name  permission_name  grantee   type_desc
-- ----------- ------------------- ---------------- --------- ----------
-- DATABASE    NULL                SELECT           testUser  SQL_USER

-- If you try to connect as the "testUser" database principal
-- you will get an error, as it doesn't have CONNECT privileges
EXECUTE AS USER = 'testUser';
GO

-- Msg 916, Level 14, State 1, Line 1
-- The server principal "testlogin" is not able to access the database "testPermissions" under the current security context.

-- Now grant the CONNECT privilege
GRANT CONNECT TO testUser;
GO

-- If you try to connect as testUser things
-- will now work as expected
EXECUTE AS USER = 'testUser';
GO

USE testPermissions
GO

-- the select permissions are already granted:
-- the query works
SELECT *
FROM someTable;

REVERT;
GO


SHRINKFILE Failure - Why does increasing file size resolve it?

I poked around in the file header page, as suggested by Martin Smith in the comments.  I think this is part of the answer, but it's mostly speculation based on observing changes to the file header page flag values between performing shrinks and other operations.

First I created a database to test with, including a secondary filegroup:

CREATE DATABASE [Shrinkfile_Test]
ON PRIMARY
(
    NAME = N'Shrinkfile_Test',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Shrinkfile_Test.mdf',
    SIZE = 8192KB,
    FILEGROWTH = 65536KB
),
FILEGROUP [SECONDARY]
(
    NAME = N'ShrinkFile_Test_Secondary',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ShrinkFile_Test_Secondary.ndf',
    SIZE = 1024KB,
    FILEGROWTH = 65536KB
)
LOG ON
(
    NAME = N'Shrinkfile_Test_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\Shrinkfile_Test_log.ldf',
    SIZE = 73728KB,
    FILEGROWTH = 65536KB
)
GO

USE Shrinkfile_Test;
GO

The I looked at "page 0" for the secondary file, which is file_id 3:

DBCC TRACEON (3604);
GO
DBCC PAGE (N'Shrinkfile_Test', 3, 0, 3);

There's a field called m_flagBits that has a value of 0x208.

If I empty this file:

DBCC SHRINKFILE (N'ShrinkFile_Test_Secondary' , EMPTYFILE);

That m_flagbits field stays the same (0x208).  Not that interesting, but now I'm in the situation you reported: if I try to empty the file again, I get this error:

  File ID 3 of database ID 19 cannot be shrunk as it is either being shrunk by another process or is empty.

I'll try growing the file (the solution that worked for you):

ALTER DATABASE ShrinkFile_Test
MODIFY FILE
(
    NAME = ShrinkFile_Test_Secondary,
    SIZE = 1025KB
);
GO

Now m_flagbits is 0x8!

At this point, emptying the file again is successful returns the value to 0x208 as you might expect.

The thing that I find interesting is that if I do this after growing the file back out (AKA flagbits value is 0x8):

USE [master]
GO
ALTER DATABASE [Shrinkfile_Test] MODIFY FILEGROUP [SECONDARY] READONLY
GO

The file is marked as is_read_only in the sys.databases table, and m_flagbits is set back to 0x208.  So it appears there is some similar file-level flag set when shrinking a file and when setting it to read only.

My best guess is that this value is used along with some other (internal) flag to indicate that a file is eligible to be shrunk.  Growing the file appears to un-set that flag (at least the one visible in m_flagbits).


Looking for help reporting duplicates and null marks

Of course.

SELECT * FROM (
SELECT  id,
        mainfield,
        location,
        COUNT (id) OVER (PARTITION BY mainfield) counter
FROM    maintable a
) where counter > 1 or mainfield IS NULL
ORDER BY mainfield;


Designing a database for "not seen" queries

The data says that you need

CREATE TABLE Seen (
    user_id ...,
    item_id ...,
    PRIMARY KEY(user_id, item_id),
    INDEX      (item_id, user_id)
) ENGINE=InnoDB;

That is in addition to table Users, with PRIMARY KEY (user_id) and Items with PRIMARY KEY (item_id).

Items not seen by user 1234:

SELECT i.item_id
    FROM Items AS i
    LEFT JOIN Seen AS s  ON  s.item_id = i.item_id
                         AND s.user_id = 1234;
    WHERE s.item_id IS NULL ;

You could then JOIN to Users and/or Items to get more info.

Separate tables for separate users -- NO.  This is a common question with the same answer every time.


MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup)

There's no tricks that can be done with ibdata1 files.
Look at using mydumper for parallel extraction and load.
pt-data-sync is a way to replicate the data with inbuilt protections against databsae delay. I don't know if it supports multisource


mysql join by 2 ways - how it works? - which gives performance difference

There is a differences because:

In first query doing the following:

create temp table with data from A filtering out by CONDITION
create temp table with data from B
for each row in temp_A we traverse temp_B. In this case temp_A using FULL SCAN, temp_B access by index

Second query

For each row from A which fall under CONDITION we join with table B.

As you can see in second query there are "preloading" data into tables and scan works in different way.


Galera == PXC == MariaDB w/Galera -- These are close to the same.
For HA, you need 1 (or more) node in each of 3 datacenters.  (Think earthquakes, tornados, floods, even datacenter outages -- which do happen.)

Oracle's Group Replication is quite new, but has a lot of promise.

Oracle's "Fabric" is probably being abandoned.  (Probably because of a hard-to-solve single-point-of-failure.)

Orchestrator is very good at failing over arbitrary trees (think relay-servers) of lots (even hundreds) of slaves.  Not sure about Master failover.  (See lectures from Booking.com.)

MHA is pretty good -- But when it discovers a "split brain" situation, it requires manual intervention.

NDB Cluster has a number of different things in its "engine".  That is, it is not a 'drop-in' replacement for InnoDB, which all of the rest support.  For one thing, it employs "eventual consistency", wherein you need to specify how to resolve conflicts.

I think the Galera-based solutions, plus Group replication are the only ones that will automatically repair a failed server when it is brought back up.  (I could be wrong here.)

My preference today:  Percona's PXC or MariaDB with Galera -- each builds in and supports Galera.  But I am keeping my eye on Group Replication.


Multiple foreign keys referencing a single table in one statement

Foreign Keys can be added in the Child Table with the reference to Unique or Primary keys of Parent Table. The columns nombre and email are NOT defined as Primary or Unique key in Cliente Table. Hence you are not able to add FKs to these columns in the table ORDEN.


MySql 5.6 error (Table 'mysql.user' doesn't exist) when started as a daemon

I've conquered such issue before, pay attention that there's an original data dir existed in x:/your_mysql_5.6_install_dir/data.

if you have defined datadir=xxx in my.ini, and the path is not the one x:/your_mysql_5.6_install_dir/data, please do the following things:

copy x:/your_mysql_5.6_install_dir/data to the destination folder.
start MySQL

e.g. if you have defined:
[mysqld]
basedir=C:/mysql-5.6.43-winx64
datadir=C:/ProgramData/MySQL/MySQL 5.6 server/data

you need to copy C:/mysql-5.6.43-winx64/data to C:/ProgramData/MySQL/MySQL 5.6 server/data

then start your MySQL.

this has been described in https://dev.mysql.com/doc/refman/5.6/en/windows-create-option-file.html:

  If you would like to use a data directory in a different location, you
  should copy the entire contents of the data directory to the new
  location.  For example, if you want to use E:\mydata as the data
  directory instead, you must do two things:

  1) Move the entire data
  directory and all of its contents from the default location (for
  example C:\Program Files\MySQL\MySQL Server 5.6\data) to E:\mydata.

  2) Use a --datadir option to specify the new data directory location
  each time you start the server.


How to declare reserved words as variables in Oracle PL/SQL?

NUM is not a reserved word.

The best way is not to use reserved words.

If you insist on using them, you can put them between quotation marks. This does not work:

SET SERVEROUTPUT ON
declare
  begin number;
begin
   begin := 10;
   DBMS_OUTPUT.put_line('The value of begin ' || begin);
end;
/

This does:

SET SERVEROUTPUT ON
declare
  "begin" number;
begin
   "begin" := 10;
   DBMS_OUTPUT.put_line('The value of begin ' || "begin");
end;
/

Quotation marks also make the variable name case-sensitive. "begin", "Begin" and "BEGIN" are different variables.


SSAS Dimension Help - Finding Values Not In the Dimension using Role Playing

In order to do what you're thinking of, you would have to make a factless fact table with the cartesian product of all possible combinations of products and the customers.  If you have a big portfolio of products this could get quite large.

In fact, you're asking a question roughly equivalent to WHERE NOT EXISTS in SQL, which you won't get out of a cube through straight slice-and-dice operations.  However, you can query it with MDX.  If you make up a little database like the one shown below and build a cube over it:

create table DimCustomer (
       CustomerID   int not null
      ,Customer     varchar (50)
)
go

create table DimProduct (
       ProductID    int not null
      ,Product      varchar (50)
)
go

create table FactSalesLeads (
       CustomerID   int not null
      ,ProductID    int not null
      ,PlaceHolder  int not null default 1
)
go

insert DimCustomer (CustomerID, Customer) values (1, 'BloggsJ')
insert DimCustomer (CustomerID, Customer) values (2, 'SmithF')
insert DimCustomer (CustomerID, Customer) values (3, 'JonesB')
go

insert DimProduct (ProductID, Product) values (1, 'Widget')
insert DimProduct (ProductID, Product) values (2, 'Dohickey')
insert DimProduct (ProductID, Product) values (3, 'Whoflungdung')
go

-- BlogsJ wants to buy a Widget and a Dohickey
insert FactSalesLeads (CustomerID, ProductID) values (1, 1)
insert FactSalesLeads (CustomerID, ProductID) values (1, 2)

-- SmithF wants to buy a Dohickey but no Widget
insert FactSalesLeads (CustomerID, ProductID) values (2, 2)

-- JonesB wants to buy a Widget and a Whoflungdung
insert FactSalesLeads (CustomerID, ProductID) values (3, 1)
insert FactSalesLeads (CustomerID, ProductID) values (3, 3)
go

BloggsJ and JonesB want to buy Widgets
SmithF wants to buy a Dohickey but not a Widget

Now, put up a cube over the database with Customer and Product dimensions and a measure group for the sales leads.  A MDX query like the following would show you which customers wanted to buy Widgets but not Dohickeys:

// Doing a 'where not exists' in MDX with set operations
//
with

 // Customers likely to buy a Widget
 set [WidgetLeads]
  as {filter ([Customer].[Customer].Children
             ,[Product].[Widget])}

 // All customers except those likely to buy a Dohickey
 set [NotInterestedInDoHickey]
  as {except ([Customer].[Customer].Children
             ,{filter ([Customer].[Customer].Children
                      ,[Product].[Dohickey])})}

// Just a bit of fluff so the list goes down  the columns
select [Measures].[Place Holder]
    on Columns

        // Intersection of the sets gives leads for a Widget who are
        // not expected to purchase a Dohickey
       ,intersect ([WidgetLeads], [NotInterestedInDoHickey])
    on Rows
  from [Leads]

The process is slightly roundabout in MDX because it does not have a 'WHERE EXISTS` operator.  You can do it in terms of set operations though, as demonstrated in the snippet of MDX.

Some OLAP front-end tools, such as the late and much lamented Proclarity would allow the user to define sets in the tool and then do the operation manually.  If you don't have access to that I can't easily think of a way that materially improves on MDX queries like this one.

One option would be to build a report that takes the combination of products you want as parameters and returns the list.  You could also implement that in SQL.

You could also possibly do something with a dynamic named set in SSAS2008+ where you calculated the set of products not present in the customers' preferences.  This would be computed as the total set of all products minus the total set of products filtered by the preferences in the set of customers.

You can calculate the sets of customers by selecting those with a particular preference or set of preferences.  Showing the non-empty members of the non-preferred list, perhaps filtered by some specific subset of interest would allow you to show them along a different axis.

This would adjust to producing a list of products where no customers in the selected set were considered to be leads.  Unfortunately I don't have a running SSAS2008 instance to hand to demo this, so it's left as an exercise to the reader.


How much does the size go down by when it does? If it is by a small amount it could be users deleting data (or replacing it by smaller data) during normal operation.

Are you compressing the dumps and looking at the size of the compressed files? The data may not always come out in exactly the same order in the dump and this could affect the compresibilty of it.


You could run something like this:

select Cast([timestamp] as date) "date", COUNT(distinct event)"nb events"
from yourtable
group by Cast([timestamp] as date)

Also, it is not a best pratice to use reserved work for column name (like user, timestamp, etc).


How to avoid multiple SELECTs from a bag of properties table?￼

Why not return the properties as key/value pairs wrapped in a single JSON value?

select jsonb_object_agg("key", value)
from settings
where "key" in ('prop1','prop2');

That can easily be wrapped into a function:

create or replace function get_properties(VARIADIC p_keys text[])
  returns jsonb
as
$$
  select jsonb_object_agg("key", value)
  from settings s
  where "key" = any(p_keys);
$$
language sql;

Then you can use

select get_properties('prop1','prop2');

Or if you do need it as columns:

select t.props ->> 'prop1' as prop1,
       t.props ->> 'prop2' as prop2
from get_properties('prop1','prop2') as t(props);


You can't reference an alias in the WHERE clause.  You'd have to do something like this:

select left(DateTimeSent, 17) as timesent
from allog_07_11_e
where left(DateTimeSent, 17) <> '0'

Likewise, you could also use a subquery:

select *
from
(
    select left(DateTimeSent, 17) as timesent
    from allog_07_11_e
) a
where timesent <> '0'


how to pass outer query param into inner in MySQL 5.7

Using this sql solve my problem:
select
(
    select sum(login_count)
    from (
        select avg(login_count) as login_count,user_id
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
    where login_count.user_id = a.user_id
) as login_count
from report_user a
group by user_id


How can I start the SSRS 2016 Report Server?

I found the solution. There is a "SQL Server Reporting Services (MSSQLSERVER)" service that manages, executes, renders, schedules and delivers reports. It was set with an "Automatic" Startup Type. After a reboot the service was indeed running but I was still getting a 503 Service unavailable error. I found a blog that says the Startup Type needs to be "Automatic (Delayed Start)". Once I changed the Startup Type to "Automatic (Delayed Start)" and restarted the service the report server started working.


Why would I need a third table in a one-to-many relationship?

I don't know what your professor has really said I am not sure if I understand your model. But maybe the following is interesting.

In the article A Logical Design Methodology for Relational Databases
Using the Extended Entity-Relationship Model by T.J Teory, D. Yang and J.P. Fry is described how entity relationship diagrams can be transformed to relations (and tables). In Fig.8f (on the p 13 of the pdf) there is the following example for an 1:n relationship:

  Each engineer can have at most one
  secretary. One secretary could work
  for several engineers.

There is a relationship "works-for" between the entities "secreatry" and "engineer".

A "secretary" "works-for" an "engineer".
Each "engineer" has at most one "secretary" that "works-for" her. It may be that an "engineer" has not "secretary" that "works-for" her.
A "secretary" can "work-for" one, more or no "engeneer"

In the article the authors propose to represent this as

ENGINEER(*EMP-NO,...,SEC-EMP-NO)
SECRETAY(*EMP-NO,...)

the fields marked by * are keys. The field SEC-EMP-NO referenes a EMP-NO from SECRETARY and is nullabel.

But there are authors that have the opinon that null values should not be allowed in relations. If one wants to avoid null values one needs a third relation to create the following model:

ENGINEER(*EMP-NO,...)
SECRETAY(*EMP-NO,...)
WORKS-FOR(*ENG-EMP-NO,SEC-EMP-NO)

ENG-EMP-NO references an EMP-NO in ENGINEER and SEC-EMP-NO references an EMP-NO in SECRETARY.

If an "engineer" always has a "secretary" that "works-for" her then the first representation with two relations does not contain null values.

Edit:
I now found the comment in your post that links to the diagrams (http://i.imgur.com/gIG2D3h.jpg)  of your professor: Actually he introduces a third table  to avoid null values as described here.


Select distance between cities in two directions

I believe your CTE already gives you the list of all distances between any two Cities, so you just need to join the CTE to itself on reversed City fields to get the reverse results like so:
WITH CityDistances AS
(
    SELECT
        CITYA_id,
        CITYA,
        CITYB_id,
        CityB,
        DIS_IN_KM
    FROM DB.dbo.DISTANCE d
    INNER JOIN cities c
        ON d.CITY_1 = c.CITY_NO
)

SELECT
    CD1.CITYA_id,
    CD1.CITYA AS [starting point],
    CD1.CITYB_id,
    CD1.CITYB as destination,
    CD1.DIS_IN_KM AS [distance in km],
    CD2.CITYA_id AS [CITYA_id 2], -- Note you can call this CITYB_id instead if that's clearer to you (CD2.CITYA_id = CD1.CITYB_id)
    CD2.CITYA AS [starting point 2],
    CD2.CITYB_id AS [CITYB_id 2], -- Note you can call this CITYA_id instead if that's clearer to you (CD2.CITYB_id = CD1.CITYA_id)
    CD2.CITYB AS [destination 2],
    CD2.DIS_IN_KM AS [distance in km 2],
FROM CityDistances AS CD1
INNER JOIN CityDistances CD2
    ON CD1.CITYA_id = CD2.CITYB_id
    AND CD1.CITYB_id = CD2.CITYA_id


How to recover from transaction replication failure?

1)Take a backup of your existing subscriber

2)Restore backup to another location

3)Reinitialize subscription

4)Load data from backup restore into reinitialized database

Going forward I would consider archiving data in another way as to not run into this problem again.


How to export a table on postgresql command line

If you want to create text files containing the data, use the COPY command (or the psql command \copy which creates the output files on the client, rather than on the server as the COPY command does).

If you want to create SQL statements use pg_dump


This is best done by LATERAL JOINs or if it is not available window functions. MySQL unfortunately doesn't support either but the latter is relatively easy to be mimicked.

> set @rownum=0;
> set @grp_lag=null;
> select id, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id
from message
order by grp_id, created_at desc;

+-------+--------+------------------+
| id    | rownum | @grp_lag:=grp_id |
+-------+--------+------------------+
| 50008 |      1 |                1 |
| 50007 |      2 |                1 |
| 50001 |      3 |                1 |
| 50004 |      4 |                1 |
| 50002 |      1 |                2 |
| 50005 |      2 |                2 |
| 50003 |      1 |                3 |
| 50006 |      1 |                6 |
+-------+--------+------------------+
8 rows in set (0.00 sec)

After having this you can have the final query something like this:

-- Set initial values for mimicing the window function
set @rownum=0;
set @grp_lag=null;

-- Query every message where rownum < per group limit
select * from (
    -- subquery providing rownumber for every group order by the created_at column
    select *, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id
    from message
    where grp_id in (1,2)
    -- Futher filtering for optimisation if possible
    order by grp_id, created_at desc
) numbered
where rownum < 50;

You can wrap this into a procedure to execute it easier. Please keep in mind, set calls also return and they need to be in the same session (yet another reason for a stored procedure).

> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)

The query above will fetch every row where grp_id matches the in condition and generate the row number for each per group. So in case of large number of messages further optimisations are necessary. For example you can heuristically filter by created_at > [estimated date for having at least 50 message].


Count values in column on join then group by

Without seeing the schema I've had to guess but it seems that you just need to add the Badge_Name to the SELECT list and the GROUP BY, like this:

SELECT t2.displayname AS UserName, t1.Badge_Name,
       Count(t1.id) AS TotalNumberOfBadges
FROM   badges t1
       join users t2
         ON t2.id = t1.userid
GROUP  BY t2.displayname, t1.Badge_Name
ORDER  BY t2.id;

If your badges table contains a different column name for the name of the badge you will have to sub that in for where I've used Badge_Name.


Best What's new book about just changes and new features in SQL Server 2012

You could always read the what's new sections in the BOL. As for a book, have you looked at Microsoft's free book? A hard copy is available too, if you prefer. The book is not a coursebook but more general an approach. As per introduction:

  Who Should Not Read This Book

  As mentioned earlier, the purpose of this book is to provide the
  reader with a high-level preview of the capabilities and features
  associated with SQL Server 2012. This book is not intended to be a
  step- by-step comprehensive guide.


Find last (max) value according to TimeStamp using update method

Your question is not clear. You are trying to update the value of some table based on a value of another? This can be solved using something like the query below:

In SQL Server 2005 or newer:

with my_data_cte (
  select row_number() over (order by timestamp desc) as sequence,
  pk_vv
from (
  select timestamp, pk_v from tb1
  union all
  select timestamp, pk_v from tb2
 )
)
update my_table
set value = pk_v
from my_table mt
  inner join my_data_cte ct on mt.pk_v = mt.pk_v
where sequence = 1


Can I Rebuild indexes in a DB except for one table?

You should implement a better solution, such as Michelle Ufford's - it will perform much more intelligent decisions on which indexes to defrag, rebuild or leave alone, and it is very easy to have exclusions:

http://sqlfool.com/2011/06/index-defrag-script-v4-1/


Is it possible to update all spatial index extents in one query?

This is generally the way that I tackle rebuilding spatial indexes in a consistent way.

This assumes that your geometries are all in the same projection and have already been indexed. It will exclude the usual ESRI Geodatabase System tables.

I avoid having ArcGIS create or maintain indexes.  They never seem to get nice extents on the projections that I use.

declare @sqlCmd nvarchar(4000)

declare cSI cursor fast_forward for
    select 'CREATE SPATIAL INDEX ' + quotename(i.name) + ' ON ' +
            quotename(sc.name) + '.' + quotename(t.name) + '(' +  quotename(c.name)  + ') ' +
            'USING GEOMETRY_AUTO_GRID WITH ' +
            '(BOUNDING_BOX = ( xmin=950000, ymin=4500000, xmax=3500000, ymax=7000000 ), ' + -- Alter to suit
            'CELLS_PER_OBJECT = 1000, PAD_INDEX  = ON, DROP_EXISTING = ON)' sqlcmd
    from sys.tables t
        inner join sys.schemas sc on t.schema_id = sc.schema_id
        inner join sys.indexes i on t.object_id = i.object_id
        inner join sys.index_columns ic on i.index_id = ic.index_id and ic.object_id = t.object_id
        inner join sys.columns c on ic.column_id = c.column_id and t.object_id = c.object_id
        inner join sys.spatial_index_tessellations s on i.index_id = s.index_id and i.object_id = s.object_id
    where i.type_desc = 'SPATIAL'  -- spatial indexes
        and (t.name not like 'GDB_%' and t.name not like 'SDE_%') -- exclude ESRI system tables

open cSI
fetch from cSI into @sqlCmd

while @@fetch_status = 0
begin
    print @sqlCmd
    exec(@sqlCMD)
    fetch from cSI into @sqlCmd
end

close cSI
deallocate cSI


I could not see any notable difference between the plan.

That's because there isn't any.
Table Scanning is Table Scanning.  As Lewis Carroll's King of Wonderland said:

  Begin at the beginning and go on till you come to the end: then stop.

The difference is in what's being done with the data:

reading the data

... takes 10 seconds.

Creating a new table based on the structure of the first table,
reading the data, and
writing the data into the new table

... takes 18 seconds.

Spotted the difference?

Reading stuff in a database is [usually] quick.  It's often just floating around in the Buffer Cache and can be quickly sent on its way.

Writing stuff in a database is slower.  It [usually] requires logging into the Transaction log first, then writing to the Buffer Cache and flushing out to disk (at some point).  It all takes time.

Consider:

Why are you creating a copy of the table at all?  If the second table contains everything in the first one, then why not just use the first one?
Do you really want all the rows?  If you're reading everything, then there's not much you can do to speed things up.  No amount of indexing can speed up a sequential Table Scan.
Do you really want all the columns?  If you're only interested in three, little, columns but are retrieving a hundred columns, full of massive BLOB data, then things will get a bit slow.


Tables

DECLARE @Land AS table
(
    [lid] integer PRIMARY KEY,
    [name] varchar(50) NOT NULL UNIQUE,
    [meter] integer NULL,
    [address] integer NULL,
    [picid] integer NULL
);

DECLARE @Owner AS table
(
    [ownerid] integer PRIMARY KEY,
    [name] varchar(50) NOT NULL UNIQUE,
    [family] varchar(30) NOT NULL,
    [mobile] varchar(30) NULL,
    [lid] integer NOT NULL
);

DECLARE @Pic AS table
(
    [picid] integer PRIMARY KEY,
    [path] varchar(256) NOT NULL,
    [picinfo] varchar(100) NULL
);

Sample data

INSERT @Land
    ([lid], [name], [meter], [address], [picid])
VALUES
    (1, 'land 1', 1, 1, 1),
    (2, 'land 2', 2, 2, 2);

INSERT @Owner
    ([ownerid], [name], [family], [mobile], [lid])
VALUES
    (1, 'owner 1 of land 1', 'family 1', 'mobile 1', 1),
    (2, 'owner 2 of land 1', 'family 2', 'mobile 2', 1),
    (3, 'owner 3 of land 1', 'family 3', 'mobile 3', 1),
    (4, 'owner 1 of land 2', 'family 4', 'mobile 4', 2),
    (5, 'owner 2 of land 2', 'family 5', 'mobile 5', 2);

INSERT @Pic
    ([picid], [path], [picinfo])
VALUES
    (1, 'Path 1', 'Pic Info 1'),
    (2, 'Path 2', 'Pic Info 2');

Solution

WITH Numbered AS
(
    -- Join the rows and number land owners
    SELECT
        L.[lid],
        L.[name],
        L.[meter],
        L.[address],
        owner_name = O.[name],
        owner_family = O.[family],
        owner_mobile= O.[mobile],
        P.[path],
        P.[picinfo],
        rn = ROW_NUMBER() OVER (
            PARTITION BY L.lid
            ORDER BY O.ownerid)
    FROM @Land AS L
    JOIN @Owner AS O
        ON O.lid = L.lid
    JOIN @Pic AS P
        ON P.picid = L.picid
)
SELECT
    N.[lid],
    N.[name],
    N.[meter],
    N.[address],
    N.[path],
    N.picinfo,
    [owner_name_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_name ELSE NULL END),
    [owner_family_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_family ELSE NULL END),
    [owner_mobile_1] = MAX(CASE WHEN N.rn = 1 THEN N.owner_mobile ELSE NULL END),
    [owner_name_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_name ELSE NULL END),
    [owner_family_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_family ELSE NULL END),
    [owner_mobile_2] = MAX(CASE WHEN N.rn = 2 THEN N.owner_mobile ELSE NULL END),
    [owner_name_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_name ELSE NULL END),
    [owner_family_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_family ELSE NULL END),
    [owner_mobile_3] = MAX(CASE WHEN N.rn = 3 THEN N.owner_mobile ELSE NULL END)
    -- Repeat the pattern up to 12
FROM Numbered AS N
GROUP BY
    N.[lid], -- key column
    N.[name],
    N.[meter],
    N.[address],
    N.[path],
    N.[picinfo]
ORDER BY
    N.lid;

Output

Execution plan

Solution description

The basic idea is that joining the three tables is easy. You don't even need an outer join at all if there is no land without an owner. The problem is that this produces rows, where you want columns in your output, with the columns numbered by owner. The numbering part is easy, using ROW_NUMBER.

Turning rows into columns is known as a pivot, the expanded form of which uses MAX, CASE, and GROUP BY as shown in the above code. You just need to repeat the MAX(CASE... sections to accommodate twelve possible owners.

There are a number of good questions and answers tagged pivot on the site already that you might like to look at. One relevant to your current problem is Need help with SQL Server PIVOT.


Mysql query - Event to delete rows by its value

To fix the syntax error:

DELETE FROM events WHERE check_in < (NOW() - INTERVAL 1 DAY)

That won't land on midnight.  Instead change to:

DELETE FROM events WHERE check_in < CURDATE() - INTERVAL 1 DAY

Give writing the EVENT a try.

Be sure to do (once):

SET event_scheduler = ON;


How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743

Try resetting the relay logs as follows:
STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000030',master_log_pos=196820914;
START SLAVE;

This will erase all relay logs in the Slave, starting with a fresh one. It should start retrieving binlog events from the Master. If it fails again, then mysql-bin.000030 on the Master may be corrupt. In that event, you would have to set up replication from scratch by doing this:

Stop the DB Application
On the Master, RESET MASTER;
mysqldump the data from the Master
Load the mysqldump on the Slave
On the Slave, run STOP SLAVE;
On the Slave, run CHANGE MASTER TO master_log_file='mysql-bin.000001',master_log_pos=4;
On the Slave, run START SLAVE;

CAVEAT
Make sure the network connection is clear (with no dropped packets) between Master and Slave.
Give it a Try !!!


SQL Server 2014 Developer on Windows 10?

SQL Server 2014 works on Windows 10 just fine. Some upgrade paths, though, will move all your apps and services to Windows.old (this happened to me one upgrade out of 4 or 5), so be careful and take the usual backups and other precautions. Thankfully, reinstalling SQL Server is not an extremely elaborate process.


When sp_foo executes there is only one query:

SELECT *
FROM (
  SELECT *
  FROM ServerB.database.dbo.Table1
  WHERE Column1 IN (1,2,3))
WHERE Column1=1;

This query can execute locally against a remote scan or it can execute remotely. What happens cannot be foretold, because it depends on the capabilities advertised by driver in use by the linked server. Typically a query like above over a linked server that uses SQLNCLI driver it will execute remotely. Local execution backed by a remote scan shows up on the execution plan as Remote Scan Showplan operator.

More complex plan can execute somehow mixed, partially remote partially locally, eg. see Linked Server behavior when used on JOIN clauses.


Any advantage of creating table in temporary tablespace - Oracle

I believe your DBA told you to use temporary tablespace for temporary data? If so using temporary tables have the following advantages:

DML on temporary tables does not generate redo. If one uses direct path INSERT (INSERT /*+ append */, CTAS) then undo is also not generated. On 12c it is possible to store UNDO in temporary tablespace as well!
after session crash Oracle will take care to remove your data
after instance crash temporary tables do not require recover

Of course I forgot something. :) People will add more advantages in comments.

While listing advantages I am not considering scenario when you have to preserve data between restarts.

Database Concepts about Temporary Tables


How to set MSDN in order to open on a certain version of Microsoft SQL Server?

I came up with a thrown-together hack with what I had on hand. If you don't have Fiddler installed, you'll need to get it from http://www.telerik.com/fiddler

Set it to Keep: 100 sessions then go to the AutoResponder tab and add this rule:

If request matches: regex:https://msdn.microsoft.com/en-us/library/ms(\d+)(\(v=sql.(100|105|110)\))?.aspx

Then respond with: *redir:https://msdn.microsoft.com/en-us/library/ms$1(v=sql.90).aspx

If you'd like to use a different version of SQL Server, then change the number at the end of the Respond With string and make sure to take that number out of the If Request Matches string or you'll end up in a redirect loop.

Enable rules and check Unmatched requests passthrough. Fiddler will have to capture traffic (F12) in order for this to function. The configuration screen should look like this:


Generate incrementing IDs without sequences

Stick to one serial column per document and create a gap-less sequence per user_id dynamically in a VIEW - if you really need it.

CREATE TABLE users (
  user_id serial PRIMARY KEY
, email text
);
CREATE TABLE document (
  document_id serial PRIMARY KEY
, user_id int NOT NULL
, document text);

CREATE VIEW document_with_rn_per_user AS
SELECT *, row_number() OVER (PARTITION BY user_id
                             ORDER BY document_id) AS doc_per_usr_id
FROM   document;

Never use basic type names like text as identifier. It's allowed, but it makes queries and error messages confusing. Generally, use descriptive names.

Related:

Compacting a sequence in PostgreSQL


Do you have any advice or suggestion?

All the operations you described are metadata-only, so I wouldn't expect any significant difference in performance.
It is strange that you don't have Foreign Keys on your [App] schema.  You've already paid the cost of checking the DRI, and if you have validated foreign keys in your database the Query Optimizer can take advantage of them.
Schema transfer would allow you to leave all the foreign keys in-place.


The answer is simple, as long as you can guarantee that self-joining the merged table will not affect all queries that hit the table.

Just merge the tables, then create a synonym for the old table names that points to the newly merged table.

eg:

create table mergedtable as ( select * from tablea union all select * from tableb );

drop table tablea;

drop table tableb;

create synonym tablea for mergedtable;

create synonym tableb for mergedtable;

The synonyms will allow the legacy code to "see" the new table as if it is the old one.


Description
  The PostgreSQL ceil function returns the smallest integer value that is greater than or equal to a number.

And according Postgres Docs:

  Except where noted, any given form of a function returns the same data type as its argument.

As you can see it works properly, in fact, if you use integers the returned value is one integer (58 * 5) / 100) returns 2 but if you forces it to decimal (58.0 * 5.0) / 100.0 then it returns 2.9.

select
     (550 * 0.5) / 100 as C1,
     ceil((550 * 0.5) / 100) as CEIL_C1,
     (58 * 5) / 100 as C2,
     ceil((58 * 5) / 100) as CEIL_C2,
     (58.0 * 5.0) / 100.0 as C3,
     ceil((58.0 * 5.0) / 100.0) as CEIL_C3
;

                c1 | ceil_c1 | c2 | ceil_c2 |                 c3 | ceil_c3
-----------------: | ------: | -: | :------ | -----------------: | ------:
2.7500000000000000 |       3 |  2 | 2       | 2.9000000000000000 |       3

dbfiddle here


How to design a database for financial bond prices?

Not strictly an answer... but... my 2cents (not interest bearing, might interest ing).

So, Security X has identifier (ISIN or CUSIP).
This identifier doesn't identify the trade, exchange, currency - just the security.

This Security has value, established by trades - which fluctuates over time, place...
This is tracked by deals. A deal requires a Security.

Price is parcel of deal.

I would create a table of Securities - Security (it has an ID int identity(1,1) PK), Code Unique constraint, CodeType (CUSIN/ISIN), linksToID (here is a funny: ISIN can have CUSIP too so link both to each other so you can identify the securities by either code)
etc.

I would create a table of Deals - Deal (it has an ID int identity(1,1) PK), time, place, and Security.ID, and TYPE).

I would create a table of Prices - Trade (it has an ID int identity (1,1) PK, DealID, Value, currency, TIME, etc)

So, I would create a unique/distinct list of securities, and insert that into Security.
Matching the Security to TradeReport (assuming the trade report to be the data you want to capture contains either ISIN or CUSIP but not neither) insert the ID of the matching ISIN of the trade data, and then inserting the matching ID of the CUSIP matching if a linked item isn't already there! to get a unique deal list.

Then I would reference the different deals back to the same import data and capture the prices of the deals to the prices table.

See the hierarchy?

from there you can splice and dice your analysis...

Yeah, that about covers my thinking... hope it helped.


Attributes in relation after cross product

To express that relation in algebraic notation you actually need to alias the attribute.

The Cartesian product, by design, does not recognize common attribute names, so much like a single operation doesn't work in a self relation scenario, it's necessary to perform a rename within the notation.

The self join example:

  R( a, b, c );

  ≠ R ⋈ R ( a, b, c );

Using rename:

  R( a, b, c );

  S = ρa/b, b/b2, c/c2 R;

  ∴ R ⋈ S ( a, b, c, b2, c2 );

For instance:

  E( EmpID, ManagerID, EmpName );

  M = ρEmpID/ManagerID, ManagerID/ManagerEmpID, EmpName/ManagerName
  E;

  ∴ E ⋈ M ( EmpID, ManagerID, EmpName, ManagerEmpID, ManagerName );

The Cartesian product can be expressed using relational algebra in the same way:

  R1( a, b, c );

  R2( a, e, f );

  ≠ R1 × R2 ( a, b, c, e, f );

So then with rename:

  R1( a, b, c );

  R2( a, e, f );

  R3 = ρa/a2R2;

  ∴ R1 × R3 ( a, b, c, a2, e, f );


What can cause higher CPU time and duration for a given set of queries in trace(s) ran on two separate environments?

That production has more CPUs does not matter when you use MaxDOP 1. Then only CPU Clock cycle matters. With many CPUs, you often get lower 1.x GHz clock cycle. A higher clock cycle on your QA could explain some of the difference.
If the database cannot fit into memory, then your hard disk speed difference sounds as if it alone can explain the different duration time.
May I recommend that you install the First Responder Kit from Brent Ozar?
https://www.brentozar.com/first-aid/
This will alert you to the most obvious problems.


Guaranteeing a FK relationship through multiple tables

The relationship, as presented in the question, could be enforced with a simple trigger.  This example relies on SIGNAL from MySQL 5.5 and later, to throw an exception and prevent the insertion of invalid data.

DELIMITER $$
DROP TRIGGER IF EXISTS albums_bi $$
CREATE TRIGGER albums_bi BEFORE INSERT ON albums FOR EACH ROW
BEGIN

  IF (SELECT record_label_id FROM artist a WHERE a.id = NEW.artist_id) !=
     (SELECT record_label_id FROM manager m WHERE m.id = NEW.manager_id) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'artist and manager must be from the same label';
  END IF;

END $$
DELIMITER ;

That's the simple version.  You'd also need a BEFORE UPDATE trigger to accomplish the same thing, preventing records from being subsequently changed to be invalid.

I've used the word "simple" twice, now, and we all know that word doesn't really belong in discussion of databases...

What about the case where an artist or a manager changes record labels?  Are the albums no longer valid?  Since this is presumably historical data over time, and presumably artists and/or managers may have moved from one label to another, it doesn't seem to make a lot of sense to have quite such a static model.

To model this data accurately, it almost seems like you'd need some kind of temporal tables that reflected the period of time in the real world when the facts stated in the database were true.

For example, an "artist_recording_label_contract" table for the periods of time during which an artist had a contractual relationship with a recording label... with something like artist_recording_label_contract_id (surrogate pk identifying the relationship between an artist and a recording label for one specific window of time), artist_id, record_label_id, start_date, end_date ... then another similar table with the same information for managers... and each album would reference the respective record in each of these time window tables when the artist and manager worked for the same label.

Then your trigger would have to validate that the artist and the manager's contract windows were not only with the same recording label, but also during an overlapping window of time... which might also need to overlap with a date in the album table if you're tracking when albums were produced.

And you'd need to potentially prevent data in the contract window tables from being updated in such a way that would invalidate any associated albums or cause windows to improperly overlap with each other, such as a manager working for the same label "from 1971 to 1976" and "from 1975 to 1980."

(So much for simple.)


Analysis

Thanks for the updates; it makes things clear.

F1E5E1E0F8E5, is סובארו when interpreted in CHARACTER SET hebrew.

CREATE TABLE `table` (
) ENGINE=InnoDB DEFAULT CHARSET=hebrew

is not sufficient -- It is possible for each VARCHAR column to override the CHARSET.  I'll assume that you have not done that.

Since the HEX in the table is consistent with CHARSET hebrew, I will  assume the input side of things is consistent.

Solution 1

The "black diamonds" are coming from the output tool.  What tool are you using?  Looks like the mysql commandline tool?  That needs to know how to display the data.  By default (I think), it interprets the bytes it gets (from the SELECT) as being UTF-8.  But they are not.  Are you running on Windows or *nix?  I don't know the details of setting it to Hebrew, but that is probably the solution.

Solution 2

Another solution is to start your 'mysql' session with SET NAMES utf8; (or utf8mb4).  That will announce that you want the bytes in the client to be utf8.  As the Hebrew chracters are coming from the table, they will be converted to utf8, and will (I think, without trying it) dislayed correctly.  Meanwhile, the table will continue to be storing F1E5 E1E0 F8E5, etc.

HTML

If you will be displaying on web pages, keep in mind that browsers make guesses on what the encoding is.  You could help them by either doing Solution 2 so that they get UTF-8, which they will readily assume, or you can stick with ISO 8859-8 and help them with <meta charset=...> and <form accept-charset...>` tags.

Please respond with the details of how you choose to solve the issue(s).  This is a scenario that is somewhat unusual.


Use large set of data in multiple databases

Use multiple schemas of one database.

You can use multiple databases, too, if you need to. That would allow a more thorough and secure separation, but you need to use functionality for cross-database access like SQL/MED or dblink. So let's keep it simple with separate schemas.

Create a master schema owned by some admin role and no access to other roles. Being logged in as admin:

CREATE SCHEMA x;
REVOKE ALL ON SCHEMA x FROM PUBLIC;

Put all your tables and data there, everything owned by your admin.

Create view(s) owned by the same admin role in each other schema where you need access, only to selected, relevant rows. Basic views are updatable by default in modern Postgres. The manual:

  Access to tables referenced in the view is determined by permissions
  of the view owner. In some cases, this can be used to provide secure
  but restricted access to the underlying tables. However, not all views
  are secure against tampering; see Section 38.5 for details.

This takes care of concurrency automatically.

As for "basic views", this was extended in Postgres 9.4. The release notes:

  E.9.3.4.2. Views

  ...
  Allow views to be automatically updated even if they contain some non-updatable columns (Dean Rasheed)

  Previously the presence of non-updatable output columns such as expressions, literals, and function calls prevented automatic updates.
  Now INSERTs, UPDATEs and DELETEs are supported, provided that they do
  not attempt to assign new values to any of the non-updatable columns.

Read the details at the link in the quote. It should work for your case: Create a "reference table" in each schema owned by the local role and join to the X tables to include only selected rows.

I suggest to additionally use WITH CHECK OPTION to restrict write operations to the scope of each view. The manual:

  The CHECK OPTION may be used to prevent INSERT and UPDATE commands from creating such rows that are not visible through the view.

If you need more sophisticated / secure policies, consider the new row security features introduced with Postgres 9.5.


Is there any better way to list all the databases in Postgres except database named postgres?

There is no real improvement possible, but I think the following is more readable:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
  AND datallowconn
  AND datname <> 'postgres';


Mongos down when I shutdown one of three servers

When you say "shutdown node-01 or node-02" what are you actually shutting down? (mongod service, config server service, the host?) Please also state what node you are shutting down in the example you gave, as well as the which node you took the mongos log from.

You can run your config server on the same host as your replica set. However, you may wish to run your config servers on separate hosts. That way, if a replica set host goes down, you don't loose a config server also. If you do loose a config server, your cluster won't be able to migrate chunk data on sharded collections. (this is what is happening in your logs, the balancer will remain disabled until your config server comes back on line.)

Note: I probably would not have apps connecting to the mongos running on the replica sets. It is fine that they are running there, admins have easy access to a mongos to connect to. But, I would run a mongos that sits outside these hosts. Otherwise a host going down in your cluster means your app, or part of it at least, is also down.

Replication is unaffected by a config server going down (just chunk migration). Do you have a sharded collection in this cluster?

Follow up:

Not sure what happened with my comment. I'll try it this way:

You can absolutely run your config servers on the same hosts as your replicas. Here is a quote from Chodorow, Kristina. MongoDB: The Definitive Guide:. Beijing: O'Reilly, 2013. page 243.

"In terms of provisioning, config servers do not need much space or many resources. A generous estimate is 1 KB of config server space per 200 MB of actual data: they really are just tables of contents. As they don't use many resources, you can deploy config servers on machines running other things, like app servers, shard mongods, or mongos processes."

Something else is going on. When you connect your mongos to the config servers, are you adding them using IP addresses or host names? What is the output of rs.conf() and rs.status() before and after shutting down the node? I notice mongos is attempting to connect on port 27017. Typically, in a sharded setup, your mongod port will be 27018 and config port will be 27019. Did you change the port numbers?

I suspect you have something misconfigured. Mongo's fail-over is pretty rock solid.


how to solve ORACLE AUDSYS.SYS space error

ORA-01691: unable to extend lob segment AUDSYS.SYS_LOB0000081269C00014$$ by 128 in tablespace SYSAUX

The SYSAUX tablespace is full.

Because the Audit system can't record what you're up to, it won't let you do anything.

Most immediately, you need [to get your DBA's] to extend the SYSAUX tablespace so that the Audit system has somewhere to write to.

Then [they need to] dig through the Documentation about how to move the Audit Trail data out of the SYSAUX tablespace and into somewhere [slightly] more manageable.

Then [they need to] dig even deeper to figure out how to get rid of the Audit trail data that you no longer want (which is probably the vast majority of it!).


I'm trying to fetch addresses for certain users only ...

IN does not scale well with big lists. Compare performance to unnest() / JOIN

SELECT a.*
FROM   unnest(ARRAY[9681,23824,23760,20098,962,14730,12294,9552,534,
                    553,5837,6768,6583,956,24179]) AS t(user_id)
JOIN   addresses a USING (user_id)
WHERE  a.type  =  'VerifiedAddress'
AND    a.state <> 'manual_verification'

I hope state and type are at least enum types. Otherwise you should normalize your database design. Have lookup-tables for state and type and only use a small IDs referencing entries there. Makes the addresses table smaller and everything faster.

Assuming all columns NOT NULL for lack of information.

Partial index

If you need it faster still, and if there are more than a few rows with type <> 'VerifiedAddress' or state = 'manual_verification', a partial index would help some more. The gain grows with the percentage of rows you can exclude from the index.

CREATE INDEX addresses_selection_idx ON addresses (user_id)
WHERE  type  =  'VerifiedAddress'
AND    state <> 'manual_verification';

Be sure to use matching WHERE conditions in your query to allow Postgres to use this index. And weigh the benefit of tailored indexes against their cost (maintenance, extra disk space, small penalty for write operations).


Copy maintenance plans to a different server

Go to the server where the jobs exist, in Management Studio's Object Explorer go to Management > SQL Server Agent and highlight the Jobs folder. Hit F7 (or View > Object Explorer Details). You should see multiple jobs in the right pane. Highlight the ones you want to copy, right-click, Script Job As > Create To > Clipboard. Now connect to the other server, open a new query window, paste, and hit F5. Much easier than mucking with SSIS.

You may have to take additional steps, depending on what the plans do. Connect to the Integration Services instance, rather than the database instance, expand Stored Packages > MSDB > Maintenance Plans, and right-click any plan and choose Export package. On the other server, follow the same steps, but right-click Maintenance Plans and choose Import package. You will have to do this for each plan.


Add a batch separator between statements.  Typically, the batch separator of choice would be GO:

--CREATE TABLE testTable (u_id int, u_name varchar(10))
--INSERT INTO testTable VALUES (1,'11')
--INSERT INTO testTable VALUES (2,'12')
--INSERT INTO testTable VALUES (3,'13')
--INSERT INTO testTable VALUES (4,'14')
--INSERT INTO testTable VALUES (5,'15')

BEGIN
ALTER table testTable ADD u_add varchar(10)
END
GO

CREATE TABLE tmp_xx_xx_testTable (u_id int, u_name varchar(10), u_add varchar(10))
GO

INSERT INTO dbo.tmp_xx_xx_testTable VALUES (6,'16','DDD'  )

INSERT INTO tmp_xx_xx_testTable (u_id,u_name,u_add)
SELECT u_id,u_name,u_add FROM TestTable

drop TABLE TestTable
GO

EXECUTE sp_rename
                N'[dbo].[tmp_xx_xx_testTable]',
                N'TestTable';
GO

--SELECT * FROM testTable
--DROP TABLE testable

The reason it fails without separators is because, before the script can be executed, it must be compiled, and in order to be compiled, all the objects referenced by each statement must exist at the compile time. The parser, however, encounters statements that reference non-existent objects. The objects are supposed to be created by previous statements in your script, but, as the script is yet to execute, they are yet to be created as well. As the result, the missing objects fail the compilation of the script.

Adding separators is equivalent to your highlighting and executing the parts of the script between the separators, except that is done automatically by the management tool (SSMS or sqlcmd), which interprets the separators prior to sending the script (or, in this case, its portions) to the server.


Sorry, I was in a hurry to ask it since it was urgent. For those who will encounter it, this may help:

Check the user that was used in the linked server. That permission in the linked server (in my case, on the test) should be set up properly.

In my case, it has no access on database_2.


Postgres Partitioning: Edge case issue on date column partitioning

The check constraints of your partitions are strange.  They don't exclude the possibility of an order from DEC2015 being inserted into the JAN2016 table.  This might not be an issue about inserting, but it makes constraint exclusion not working nicely.  Fix this by specifying a beginning date, too.

That aside, the problem of having an order and its lines in the corresponding partitions can be easily solved.  Things depend a bit on how the submitteddate is calculated.

First, in the simple case I would not add such a date to the order lines - the timestamp of the order usually defines the former.  However, if there is a possibility to add lines later, your problem is inherently unsolvable: you either cheat and register lines from the next month with an earlier date, or have lines for a given order in different partitions.

On the technical side, ensuring that all dates are from the same day is easy.  Use a transaction to add the order lines alongside the order (this makes sense in any case), and in this case CURRENT_DATE will return the date of the start of the transaction, even if the last insert flow over into the next month.


Howto create useful dacpac versioning along with SSDT deployment?

We finally solved this by adding the build numbers in short and long format to

a) The target database as user-defined extended properties

b) A versioning table


Different Results From sys.databases As Different Users

This appears to be a matter of access rights and the specific user in the database.  The first thing to understand is that sys.databases in any database is simply a view that exposes parts of the system table that lives in resourcedb.  Anytime you are querying that in any database, you're hitting the same table but through a different view.

The second thing to understand is each database has a dbo user.  When you use the execute as user = N'dbo', that means you're dbo in the context of that specific database.  In your specific example, the dbo user in master and the dbo user in tempdb are two different security principals.

What you're seeing is simply a matter of what each principal is allowed to see.  dbo users, being the owner login of each specific database, only have rights to see the following databases:

master (in order to look up control information)
tempdb (in order to create temporary objects when necessary)
The database they own

Note that the dbo user in master, since that database needs to see and supply control information for all databases, has rights to view all databases on the server.


How can I connect to a database after its disk has come back online?

First, tell your customer to get a real disk. Using a remote disk this way is exactly why you are having difficulty at this stage, and it could very well lead to a corrupt / unrecoverable database. You are rolling the dice every time this drive goes offline, even if only temporarily.
Next, check the status of the database:

USE master;
GO
SELECT state_desc
  FROM sys.databases
  WHERE name = N'your_database';

If this if OFFLINE, and you're sure the drive is available, run:

ALTER DATABASE your_database SET ONLINE;

Now, this might not work - as I suggested the database could come up SUSPECT or it might not come online at all.
If / when it does come back online, take a full backup. Schedule these (and/or log backups if in full recovery) to run much more frequently, to cover your butt.
Tell your customer to get a real disk. This may work in the short term but this is a ticking time bomb.


Recommended MySQL statistics for production environment

I'd highly recommend using MONyog.

  MONyog MySQL Monitor and Advisor is a "MySQL DBA in a box'' that helps
  MySQL DBAs manage more MySQL servers, tune their current MySQL servers
  and find and fix problems with their MySQL database applications
  before they can become serious problems or costly outages.

Our DevOps team uses this extensively for both production and development. These guys have most of the "best practices" baked into the application, so we really don't need to get our hands too dirty in the DBA stuff.


use this query it will change sa password

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'newpassword'
GO


Auto-generate scripts to create tables, indexes, etc

SQL Server Management Objects SMO is your answer. You can use it to accomplish this task. Here is an example to generate Create Table Scripts.

    public string GetTableDescription(string pDatabaseName, string pSchemaName, string pTableName, string connectionString)
    {
        connectionString = ConnectionHelper.RemoveProviderFromConnectionString(connectionString);
        Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));
        Database db = server.Databases[pDatabaseName];
        Table t = db.Tables[pTableName, pSchemaName];
        ScriptingOptions baseOptions = new ScriptingOptions();
        baseOptions.NoCollation = true;
        baseOptions.SchemaQualify = true;
        baseOptions.DriDefaults = true;
        baseOptions.IncludeHeaders = false;
        baseOptions.DriPrimaryKey = true;
        baseOptions.ExtendedProperties = true;
        //baseOptions.DriAll = true;
        //baseOptions.Indexes = true;
        //baseOptions.DriAllKeys = true;
        //baseOptions.SchemaQualifyForeignKeysReferences = true;
        baseOptions.EnforceScriptingOptions = true;
        StringCollection result = t.Script(baseOptions);
        return StringCreate(result);
    }


How indexing on a date field that is constantly updated and or omitted affect performance?

Hi It will affect but not greatly.what happens is whenever you made change in collection index will be updated and during index update your collection will be locked .So to overcome this you can use index in background  by using:
db.collection.createIndex( { last_updated: 1}, {background: true} )

it will let you use your collection while updating so you will never found any kind of disruption because of your index.

As you have mentioned that you do sorting on "last_updated".I strongly recommend you to use index .you will have high read performance at a minimal indexing cost.


How to count the number of times an attribute appears in an inner query?

Something like this.

The below lists everything: which student took which course  taught by which professor.

select
  s.student_id, c.course_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;

But we do not care about courses. The below lists which student is taught by which professor(s). (student_id, course_id) is unique, and 1 course is taught by 1 professor, so the same (student_id, professor_id) values appear multiple times, if the student attended multiple courses taught by the same professor.

select
  s.student_id, p.professor_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
;

We do not care about professor_id and we need to list students taught by more than 1 course.

select
  s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) > 1
;

This may list the same student multiple times because a student can take 2 courses from a professor and 3 courses from another professor, so list 1 student only once:

select
  distinct s.student_id
from
  students s
  join takes_course tc on (s.student_id = tc.student_id)
  join course c on (c.course_id = tc.course_id)
  join professor p on (p.professor_id = c.professor_id)
group by
  s.student_id, p.professor_id
having
  count(*) > 1
;


It probably isn't possible to do this robustly.

If your previous sequence ends in:

"01-01-2014", "ABCDEFG", "+100,00", "+1024,56"

and the next sequence begins with:

"01-01-2014", "ABCDEFG", "+100",   "+1024,56"

is that the same transaction as before? Or is the data really:

"01-01-2014", "ABCDEFG", "+100",   "+1024,56"
"01-01-2014", "ABCDEFG", "+100",   "+1024,56"

so you should import two transactions?

Without a timestamp, some useful unique transaction identifier, or the ability to know for sure what point you've already imported up to (say, transactions being split into discrete and non-overlapping monthly statements) I don't think you can do this reliably.


Postgresql extremely slow count (with index, simple query)

Fetching and counting 5 million rows is slow business.

There are two problems:

The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with one bit per table row.

It then degrades to storing a bit per 8KB block, which leads to more heap fetches in the bitmap heap scan phase to sort out the false positives.

So raising work_mem will speed up the execution, but according to your comments not a lot.
Rather than consulting the table itself, which is slow, PostgreSQL could also read only one of the indexes, because they contain all the required data.

That would be much faster, so why does PostgreSQL not do that?

The explanation is that PostgreSQL tables and indexes contain row versions (tuples) that are visible and tuples that are not visible (because they have been deleted or updated and not vacuumed yet). These “dead tuples” are required for PostgreSQL multi-versioning implementation.

Now the information which tuples are visible and which are not is only stored in the table (the heap), not in the index. So PostgreSQL has to consult the heap to figure out which tuples to count and which not.

If you run VACUUM on the table, PostgreSQL will update a nice thing called the “visibility map” that contains one bit per 8KB block that indicates if all tuples in the block are visible to everyone or not.

If the visibility map has a block as all-visible, the index scan doesn't have to consult the table to determine if it can see a tuple in the block or not. If that works for most tuples, you get an index only scan which is obviously much faster.

So you should see that the table is vacuumed often enough.

For that, make autovacuum run faster and more often on this table:

ALTER TABLE subscriptions SET (
   autovacuum_vacuum_scale_factor = 0.05,
   autovacuum_vacuum_cost_delay = 2
);

You would be even faster if you had an index on only project_id.


Deny INSERT/UPDATE/DELETE to a particular schema

Usually you just put the users in an AD group and add the group to SQL as a named user.

Then grant the named user select/execute privileges by schema.

This is how you grant/revoke privileges.

Let's say we have a user abc and he uses schema xyz. Here is the syntax.

GRANT Insert ON xyz TO abc;

Replace 'GRANT' with 'Revoke' and 'Insert' with ('Update' or 'Delete') according to user requirements.

You can revoke all of three at once together too.

Revoke INSERT, UPDATE, DELETE ON xyz TO abc;


PostgreSQL: Index Trigger / Hook? For Synonyms

It's all preprocessing. PostgreSQL can do it's thing. You can do yours.

CREATE INDEX ON myTable
  USING gin ( myTransform(x) );

SELECT *
FROM myTable
WHERE 'foo & bar' @@ myTransform(x);

Exercise left to the OP,

CREATE FUNCTION myTransform ( x string )
RETURNS tsvector AS $$
  BEGIN
    -- modify x
    RETURN x::tsvector;
  END;
$$ LANGUAGE plpgsql
IMMUTABLE;


MySQL query cache with time based expiration

I'll assume your database is all InnoDB when answering this question.

What you are asking for is not possible for the Query Cache. The mechanisms you have in mind do exist with the InnoDB Buffer Pool.

What you should try is the following:

Disable the Query Cache (Why query_cache_type is disabled by default start from MySQL 5.6?)
Make the InnoDB Buffer Pool Resistant to Scans

Increasing innodb_old_blocks_pct
Increasing innodb_old_blocks_time

This controls what and how long blocks of data and index are to stay inside the Buffer Pool

To release blocks after a while, you would change innodb_old_blocks_pct back to 37


Finding Damage Done by FOREIGN_KEY_CHECKS=0

The way to check for "incorrect" foreign keys, you have to do something like:

SELECT
   bar.*
FROM
   bar
   LEFT JOIN foo ON bar.foo_id = foo.id
WHERE
   bar.foo_id IS NOT NULL AND foo.id IS NULL ;

or its equivalent

SELECT
    bar.*
FROM
    bar
WHERE
    bar.foo_id IS NOT NULL AND
    bar.foo_id NOT IN (SELECT foo.id FROM foo WHERE foo.id IS NOT NULL) ;

There is a tool, written in Ruby, called mysql-foreign-key-checker, by Michael Irey that can do this kind of checks systematically for you. The program is actually quite simple and easy to understand, and could be translated  quite easyly to the language of your choice.

Check the queries at SQLFiddle.

NOTE: I know that some IS NOT NULL shouldn't be necessary in the queries, because you already have NOT NULL constraints. But... if you're checking for data that actually violates some constraints... you'd better not rely on any constraint.


Single table of all chat conversations or separate tables?

If this is an actual service that you would like to create and not just an academic exercise, then it would be best to start with some realistic expectations and grow from there. Many years ago I implemented something like this at the day job where employees could communicate with one another in a Microsoft Teams-like fashion (though before Teams was a thing). The system is still being used today and sees anywhere between 5 and 25 messages per minute between the hours of 7:30am and 2:45am local time. It is used by about 18,500 employees scattered across the globe.
Current statistics (as of 00:00:00 UTC today):

Item
Number
Note

Posts
35,641,804
As per HR and Legal requirements, no records are deleted, but they are instead "soft deleted".

Files
2,613,017
Same as above

Channels
74,331

Accounts
24,459
6,000+ idle/expired accounts

Each one of these items has its own table, and there are additional tables that enable additional functionality, such as ChannelMember, which specifies which Accounts are members of a Channel and what level of permission they have (Read-Only / Read-Write).
Posts are saved in Markdown format and have no realistic size limit. People are free to use the space as they see fit and, so long as nobody reports an Account or Channel as being inappropriate, no communications are monitored or shared with management.
Search is handled by splitting posts apart on a word-by-word basis and storing unique words in a PostSearch table, which is then used for somewhat faster lookups. Because the words are split apart, search results can be "weighed" for their relevance before being returned to the requester.
This runs on a MySQL database with a PHP-based API handling all communication between the front-end applications and the database.
Now the fun stuff:

Action
Average Response Time

Loading Channels a Person Belongs To
0.18 seconds

Loading a Timeline view of recent Posts
0.21 seconds

Loading the most recent 250 Posts in a Channel
0.13 seconds

Publishing a new Post
0.51 seconds

Searching for Posts with 3 terms
1.33 seconds

Searching for Posts with 5+ terms
1.61 seconds

These are API response times, meaning requests are first authenticated, processed, then returned in a JSON format.
The database server is running MySQL 8.0.23 and Ubuntu 20.04 LTS on a db.m6g.xlarge instance on AWS. There are three web servers, all running Apache and Ubuntu 20.04 LTS on t4g.medium instances.
Unless you have tens of millions for marketing a hot new service that will supplant the existing big players, you may not need to over-think the structure just yet. Build something specific and see whether it catches on or not. If it does, then you can look at getting it to scale.


What is the status of the PostQUEL source code?

The database was not known as POSTQUEL, but as POSTGRES. PostQUEL is "an incompatible, extended version of QUEL (the Ingres query language)."

As to the source code, I found it linked from this Command Prompt Blog Post,

You can find it available on the POSTGRES Project page at Berkeley.


Archiving process not running fast enough

Doing an insert from the source side through a linked server is problematic for performance.  Each row inserted into the target is inserted via a discrete INSERT statement inside a cursor operation.  i.e. if you are inserting 12,000 rows into the target, SQL Server will actually execute the insert 12,000 times, once for each row.

If you re-write the archive process so it runs from the destination server, it will be much faster.

As an example from your execution plan, rewrite this:

INSERT INTO @MessageEvent (ID, DateTime)
SELECT TOP (1500) ID, TimeStamp
FROM ConnectAPI.dbo.MessageEvent (NOLOCK)
ORDER BY ID

and run it from the destination server, like this:

INSERT INTO ConnectAPI.dbo.MessageEvent (ID, DateTime)
SELECT TOP(1500) ID, TimeStamp
FROM [SourceServer].ConnectAPI.dbo.MessageEvent
ORDER BY ID

Notice I left the NOLOCK query hint out.  You may want to make sure you understand the implications for partial reads and/or duplicate reads under READ UNCOMMITTED isolation, which is the isolation level used with the NOLOCK hint.

I wrote a blog post at SQLServerScience.com with minimally complete, verifiable example code showing how a simple insert of 800,000 rows from a source server to a destination server takes over 7 minutes when executed from the source server, and only 11 seconds when executed from the destination.


Cost of replaying transaction log on sql server HA replica

But it got me wondering, which is actually doing more work? The primary or the replicas?

The Primary.  The secondary doesn't have to run the SELECT,INSERT,UPDATE, and DELETE statements for the sessions that are modifying the database.  The log records resulting from those operations are sent to the secondary which copies them to its log file and then applies them to the database using the REDO process.

The Secondary can sometimes perform more IO than the primary, as the primary writes the log file, and lets the Checkpoint/Lazywriter write the database.  But overall the Primary does more work.

  That would mean the replica would need to do a key lookup for each row instead of an index scan, which would be a lot more expensive.

The secondary doesn't apply the logical operation.  The log record indicates exactly what modifications are made to the database pages.

  Also, are the physical pages of the replicas exactly identical to the primary at the byte level?

Yes.  In fact a if the primary discovers a corrupt page, it will repair the corruption by getting an uncorrupted copy of the page from a secondary.  This is called Automatic Page Repair


Mongodb uses mmap for memory management, relies 100% on the OS memory management. This means whatever memory you have left it has a really high possibility will be eaten up by mongo cause the entire datafile structure is mapped to virtual address space (It is not certainly a problem while the paging between physical and virtual memory is not that dumb to break your system down).

While Linux will page out some parts of the physical memory, so not the whole virtual segment have to be in physical memory just the recently accessed parts (check this), depend on the usage pattern mongo can run without problem.  If the OS will have to extensively page things mongodb will be slow.  You can restrict the usage of the memory. I would recommend to use cgroups. For your probable workload memory usage you can check also this documentation to have some idea.


Benefits of table partitioning on same disk

It can help query performance by employing partition elimination. This means large sections of big tables can be ignored when looking for values which means much less IO. Index alignment needs to be looked into when partitioning. See details here

You can break your backups by partition. This can be useful if you are struggling to complete your backups in time. See here for details

Index rebuilds can be done at the partition level instead of the whole table.

Large inserts can be done with partition switching.

Queries can also run parallel across partitions.


How to find archivelog gaps in the backups

I think that REPORT is the appropriate command, but at the moment I cannot test it. Before you execute the report command you should execute a CROSSCHECK command so that rman does actually know which backups exists and which are missing.

If you want to find out if there are archive log backups missing then

REPORT NEED BACKUP DATABASE;

should do the job. The whole database must be backed up because of the missing archive logs the database cannot be restored to the current point in time. So the command will report all data files.

If backup pieces were destroyed that contain datafiles of the morning full backup then

REPORT NEED BACKUP DAYS 1 DATABASE;

So this command will report both problems.

The report-command is documented in the Backup and Recovery Reference and in the Backup and Recovery User's Guide.

So all in all do

CROSSCHEK BACKUP;
REPORT NEED BACKUP DAYS 1 DATABASE;


Appears this feature is deceptive and what you're trying to do is not yet possible. Slated for v3.

https://issues.apache.org/jira/browse/CASSANDRA-6377


Can I delegate SQL Agent rights without granting sysadmin rights?

Unfortunately, administration of Agent jobs can currently only be done by members of the sysadmin role.  The relevant documentation is found on MSDN.  You'll note that the three Agent roles (User, Reader, and Operator) have different levels, but ultimately a user without sysadmin can only edit/manage jobs that user owns and not jobs created by other users.


How do you SUM a column without having its name?

Many (perhaps, most) other database products will not allow you to define a derived table with a calculated column that has no explicitly assigned alias. In other words, they will not let you have this

SELECT
  ...
FROM
  (
    SELECT
      expression
    FROM
      ...
  )

Instead, you would have to do something like this:

SELECT
  ...
FROM
  (
    SELECT
      expression AS SomeAlias
    FROM
      ...
  )

The reason is, they do not assign a name to an expression automatically. In contrast, Oracle does do that, and that is why Oracle is fine when your derived table has a calculated column without an explicit alias. That column will have a default name. The only problem is, what that name will be.

Basically, the name is the expression itself but Oracle applies certain transformations to the original expression before using it as a name. In particular, all unquoted identifiers (including names of functions) are uppercased and all spaces are removed. Thus, if the expression is count(*), the default name of the column becomes COUNT(*). If the expression is a + 1, the default name will be A+1. Note that the presence of special symbols in both those example ((, *, ), +) prescribes that you enclose the name in quotation marks to be able to use it as a reference. In other words, if you have a derived table like this:

  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

then you must write

SELECT
  "COUNT(*)"
FROM
  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

rather than

SELECT
  COUNT(*)
FROM
  (
    SELECT
      COUNT(*)
    FROM
      ...
  )

if you want to reference the derived table's column; otherwise Oracle will be interpreting the reference not as a reference but as an actual expression (another call of the COUNT function, this time applied to the output of the derived table).

So, in your specific example, you can reference the derived table's first column as "COUNT(*)":

select sum("COUNT(*)"), employID from
    ( select count(*), employID from  table1...
           union all
      select count(*), employID from  table2...
           union all
      select count(*), employID from  table3...
)

That said, why avoid assigning an alias explicitly? Often it is not about how you want to do things but rather about how things are meant to be done. Besides, if using AS name immediately after the expression is for some reason unacceptable, you have other options of assigning a column alias, as suggested in the answer by ypercubeᵀᴹ.


How Do I Gain SysAdmin Access Without Having a SysAdmin Account?

Yes there is please follow below Microsoft web resource.It requires you to have admin access to windows machine and then start SQL server with -m option and create login in SQL server and grant it admin privileges.

http://msdn.microsoft.com/en-us/library/dd207004.aspx

Hope this helps


How can I use a full-text search on a jsonb column with Postgres?

PostgreSQL 10+

PostgreSQL 10 introduces Full Text Search on JSONB

CREATE INDEX ON table
   USING gin ( to_tsvector('english',jsondata) );

The new FTS indexing on JSON works with phrase search and skips over both the JSON-markup and keys.


Database snapshot "The requested operation could not be completed due to a file system limitation"

The issue is around NTFS allocation maps and the amount of space they have for file allocations. In general, the more fragmented the file the more allocation mappings there needs to be.

By default when a volume is formatted for NTFS, small allocation map size is chosen (1024 bytes) whereas with /L the large allocation map size is chosen (4096 bytes).

Normally this doesn't come into play or show itself and any appreciable way... However once volumes get larger and larger or use technologies that create many small random allocations (such as database snapshots or checkdb) then it can rear its' ugly head.

To get around this, a few different options can be chosen:

Format the volume ntfs with /L:Enable
Make smaller volumes so that this doesn't happen
Use ReFS if on SQL 2014+

In most cases, choosing to format with /L:Enable will be the easiest and fastest option.


Users on same server connect to MySQL with different hosts

Adding the IP and host name to the hosts file on the database server seems to have resolved this problem!


The following expression will give the bit representation of today's day of week:

POW(2, 7 - DAYOFWEEK(NOW())

POW(2, n) raises 2 to the nth power, which is the same as shifting 1 n bits from the right.
DAYOFWEEK(date) returns the day of the week as a number, with Sunday = 1.
Since you have the low-numbered dates in the high-order bits, 7 - DOW converts that to Sunday = 6, Saturday = 0.

Wouldn't it be easier to declare the column as a SET, though? Then you could just use FIND_IN_SET().


How to get query execution time and query result at the same time?

I agree with @DTest, how are your running your query? If you log directly into a MySQL terminal style and hand-run queries,  it will give you a time result. Also, if you use Oracles MySQL database tool (I believe it is called "MySQL Workbench"), you can connect to your database server directly and the GUI will also give you time results for the query if you hand-run it.


It looks like the standby doesn't successfully connect to the master.

In order to be able to use the streaming replication, the master needs to have enough WALs in the streaming buffer that it can send to the standby.
Since you're not using log shipping, it needs to store all the logs from when you started the backup to when the standby has been started.

You can control the number of WAL's stored in the buffer by setting wal_keep_segments to a high enough value.

You should also make sure that max_wal_senders is set to the number of standby servers you have.

However, it is always best to have the WAL restore set as a backup for when the streaming replication doesn't work for any reason. You can achieve that by setting the archive_command in postgresql.conf and the restore_command in recovery.conf

For instance like this, assuming that /pgsql/backups/archive_logs is mounted as a nfs share accessible on both servers:

archive_command = 'cp %p /pgsql/backups/archive_logs/%f'
restore_command = 'cp /pgsql/backups/archive_logs/%f %p'


Depending on the database you're using, you could have an ON INSERT trigger that checks if there are >= 50 records in prices and then runs the recalculate-and-update process (you don't have to do all of that in the trigger itself, you could separate it out into a stored procedure that is called by the trigger).

As for prices and temp_prices, I don't see anything terribly wrong with it. I imagine that the structures are the same, but they are being used in very different ways. If you did want to merge the tables, you would probably need to have an indicator of some sort to show which records came in from the users, and which ones were there before. Considering that there will be different kinds of activity on these two types of prices, it might make sense for performance reasons to leave them in separate tables.


David Hay's

Enterprise Model Patterns. This is a beast of a book, but has some great patterns.
Conventions of Thought. More stuff on MRP.
A Meta-Data Map . Haven't read this one.

Len Silverston's

Data Model Resource Book Vol. 1. Your main data model patterns.
Data Model Resource Book Vol. 2. Case studies by industry.
Data Model Resource Book Vol. 3. A deeper explanation of Vol 1.

Michael Blaha's Patterns of Data Modeling. This one has some interesting temporal, graph, and tree models.

Martin Fowler's Analysis Patterns. This one skims some of the other patterns, but gives accounting a solid treatment.

They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on safaribooksonline.

Also, OASIS's Universal Business Language, schemas


SQL Server 2008 can't repair consistency

You need to restore the page from a full backup, then applying the logs forward to current or you need to do a full restore.

I'd also run chkdsk as it appears you might be having a disk problem as the same page is reporting problems when attempting to repair it.  Also run any disk management tools and see if they report problems with the disk.


Running the .sql script in Linux to get the postgresql database table and functions

You are trying to run the psql as the OS user 'root'.  Your pg_hba.conf file doesn't let OS user 'root' log in as database user 'postgres'.

You could instead use sudo to run as OS user 'postgres', not 'root':

sudo -u postgres psql -U postgres -d tsdb -f /.sqlscript/location

(Although perhaps OS user 'postgres' won't have access to the sqlscript)

You could also make use of a mapping file pg_ident.conf to say that root is allowed to log in as postgres, although I don't see much point in that.

Or you could change the "local" line of your pg_hba.conf so that it uses "md5" rather than "peer".  At that point, there is no reason to use "sudo" at all as it doesn't care who you are, just that you know the password.

Or you could make use of the existing 'host ... md5' line of your pg_hba.conf, by specifying a host rather than defaulting to using a unix socket:

psql -h 127.0.0.1 -U postgres -d tsdb -W -f /.sqlscript/location


Property Owner is not available for Database 'DBNAME'

myUser is probably already a database user.  You shoudl be passing a login principal to the stored procedure.  Do something like this:

use DBNAME
go

exec sp_changedbowner 'myLoginThatShouldBeDBO'
go


Splitting a database into multiple databases

If the problem is this single table getting huge, you should consider partitioning it (by date, since your data seems to be time-based).

This way the SQL engine query planner may be able to ignore some partitions (if they contain data too old to appear in your query results) when querying and thus perform better.

Additionally, partitionning will be handled transparently by your database engine, so you have nothing to change in your code.

Another advantage would be that you can imagine to move the 'old' partitions, containing data that is rarely accessed, to slower (and cheaper) storage.

You can also set up your indexes to be partitioned so you can only rebuild a part of an index:
http://msdn.microsoft.com/en-us/library/ms187526.aspx

Index partitioning is a different feature from table partitioning, it can avoid you to have to rebuild an entire index if it's not needed.


From here, you obtain the command to change the setting for sql_safe_updates (from the mysql client command line). It's fairly simple - SET SQL_SAFE_UPDATES=0; (or 1), though as you point out, it can't be modified in my.ini or my.cnf.

However, from this post on StackOverflow, you have a method to run SQL (and server) commands on server startup.

Briefly, you create a file called (arbitrary) <my_file_name>.sql and put the command above setting the sql_safe_updates in that file.

Then, in the [mysqld] section of the my.ini (or my.cnf), you put the line

init-file="C:/xxx/yyy/<my_file_name>.sql"

To answer your specific question, there is a reason that the default is ON.

This is because many software developers are not very proficient in SQL. To compound this problem, many of them use frameworks which produce SQL which is (ahem...) not optimal. If one ran a command

DELETE FROM TABLE <no where clause>

on  a production system, this could cause havoc (and has done, judging by the number of posts here pleading for help with a deleted table).

Note: I tested this on Windows (don't have Linux up and running at the moment).


Should I drop a btree index on a column after adding a unique constraint?

If the unique constraint is defined on the same columns as the index, and the columns are in the same order, the index is redundant with the index created by the constraint and can be dropped without any problems.


calculate percent change between two entire rows


Is it possible to limit the maximum disk usage for a user

If your users have create permissions in their own schema, then you can set up a DDL trigger to capture table creation that occurs on a different filegroup than the one you've associated with that user. You can't prevent the table from being created, but you can roll it back and return an error message.

First, let's add a filegroup and a file with a max size to an existing database:

USE [master];
GO

ALTER DATABASE floob ADD FILEGROUP SomeUser;
GO

ALTER DATABASE floob ADD FILE
(
  NAME = SomeUser,
  FILENAME = 'C:\...wherever...\floob_SomeUser.mdf',
  MAXSIZE = 20MB -- your quota, this part is important!
) TO FILEGROUP SomeUser; -- this part is also important!
GO

Now let's create a schema and a login that can control that schema:

CREATE SCHEMA SomeUser;
GO

CREATE LOGIN SomeUser WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

USE floob;
GO

CREATE USER SomeUser FROM LOGIN [SomeUser] WITH DEFAULT_SCHEMA = SomeUser;
GO

GRANT CONTROL ON SCHEMA::SomeUser TO dbo;
GRANT CREATE TABLE TO SomeUser;
GO

First of all, with these settings, this user can't create tables in dbo or any other schema other than their own, which means by default they will only be able to create tables on their limited file[group]. If you connect to the database floob as this user and try to create a table in dbo:

CREATE TABLE dbo.foo(id INT);

You get this error:

  Msg 2760, Level 16, State 1, Line 1The specified schema name "dbo" either does not exist or you do not have permission to use it.

And because of this user's default_schema, not specifying the SomeUser schema will still end up with a table created in SomeUser (though you really should be pushing for best practices here, especially in this case, and always specifying the schema name when creating or referencing objects).

Now we can create a trigger like this, which will roll back the entire batch in the case where a filegroup is not specified:

CREATE TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') = N'SomeUser'
     AND NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = N'SomeUser'
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))

  BEGIN
    RAISERROR('You must place your objects on the SomeUser filegroup.', 11, 1);
    ROLLBACK TRANSACTION;
  END
END
GO

It is particularly handy to keep the user, schema and filegroup names the same, which will make it easier to have this as programmatic. You can first perform a check that the schema being used is not one of your "umlimited" schemas, then proceed with a check that compares the schema name to the filegroup name. If it doesn't match, this user has rights to the wrong schema and is trying to create objects in the wrong place, and an error will be raised:

ALTER TRIGGER EnsureFilegroup
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
  DECLARE @e XML = EVENTDATA();

  IF @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') <> N'dbo'
    -- or NOT IN (N'dbo', N'other unlimited schemas')
  BEGIN

    IF NOT EXISTS (SELECT 1 FROM sys.indexes AS i INNER JOIN sys.data_spaces AS d
       ON i.data_space_id = d.data_space_id
       WHERE d.name = @e.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)')
       AND i.[object_id] = OBJECT_ID(
        QUOTENAME(@e.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(128)'))
        + '.' + QUOTENAME(@e.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(128)')))
       AND i.index_id IN (0,1))
    BEGIN
      RAISERROR('You must place your objects on your own filegroup.', 11, 1);
      ROLLBACK TRANSACTION;
    END
  END
END
GO


Yes, it's possible.

SQL> alter database close;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database dismount;

Database altered.

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-01507: database not mounted


Why COUNT(*) on linked server is inaccurate?

Some of the things that could describe a different count on the same table (remote or not) are:

query a different table without knowing it due to default schema
query at a different time, where data changes have happened in the meantime
query with different isolation semantics (e.g. using NOLOCK or snapshot during data changes)
query a table with row-level security
query a view with filtering of any kind
query a synonym that actually points to a different object

Other explanations are usually that they are not the same table. Does server B have a loopback linked server to server B? Or did you execute a slightly different query on server B? Is it possible it was in a different database, or that the linked server on server A might be pointing at something else?