Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'?

It is an update trigger on a table which contains point geometries.

It has fields called 'easting' and 'northing'. These fields need to converted from integer to double precision due to this error code on saving of the table.

Provider errors:

PostGIS error while adding features: ERROR:  invalid input syntax for integer: "387733.568253859"
CONTEXT:  PL/pgSQL function "tf_xy_27700" line 5 at assignment'*

So I have converted them to double precision. I now need to get these fields to update with the x/y on update.

See below for my code so far:

CREATE OR REPLACE FUNCTION tf_xy_27700()
    RETURNS trigger AS
$BODY$
    BEGIN
        IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
            new.easting := ST_X(new.wkb_geometry);
            RETURN NEW;
        END IF;
    END;
$BODY$
LANGUAGE plpgsql;

its okay, and sorry for the bad explanation.

i got it to work as shown below:

CREATE OR REPLACE FUNCTION tf_xy_point()
  RETURNS trigger AS
$BODY$BEGIN

IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN

    new.easting := ST_X(new.wkb_geometry);

    new.northing := ST_Y(new.wkb_geometry);

RETURN NEW;

END IF;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tf_xy_point() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO public;
GRANT EXECUTE ON FUNCTION tf_xy_point() TO postgres;

This now allows for the trigger to used as generic for any other table wishing to find the eastings and northings on UPDATE.


SQLite, ASCII Characters 0x20 to 0x7E Check Constraint I have the following table which I'm trying to limit the column name to ASCII characters ranging from 0x20 to 0x7E. Why is it not working?

CREATE TABLE test
(
    id INTEGER NOT NULL,
    name TEXT NOT NULL CHECK(name NOT GLOB '*[^\x20-\x7E]*'),
    PRIMARY KEY(id)
)

In SQL, \ is not a special character.

You have to insert the characters directly:

CREATE TABLE test
(
    id INTEGER NOT NULL,
    name TEXT NOT NULL CHECK(name NOT GLOB '*[^ -~]*'),
    PRIMARY KEY(id)
)


How to get paged resultset from a table? I am trying to get paged resultset from a table (Notes). So, I am passing the last offset id (@LastOffset) value along with the page size (@Count). My approach to achieving this is:

Sort the table on create date with the most recent dates at the bottom (ASC), so that the changes to the table do not interfere with the overall result.
Get the row number of the row that contains the last row id from the previous page, i.e. @LastOffset.
Get the next set of rows as per the @Count parameter.

I have the following example:

-- Notes table sorted by Created date ASC
NoteId BookId Username Created     NoteText
1      2      abc      01/01/2015  Note 1
2      2      abc      01/01/2015  Note 2
3      2      abc      01/01/2015  Note 3
4      2      abc      01/02/2015  Note 4

I need to create a Stored Procedure that will return the following result given parameters: @LastOffset = 2, @Count = 2

-- Result set
NoteId BookId Username Created     NoteText
3      2      abc      01/01/2015  Note 3
4      2      abc      01/02/2015  Note 4

This is what I have so far:

    DECLARE @StartOffsetRow int
    SET @StartOffsetRow =   (SELECT RNUM
                            FROM (
                                    SELECT ROW_NUMBER() OVER (ORDER BY Created) RNUM, NoteId
                                    FROM Notes
                                    WHERE BookId = @BookId AND Username = @Username
                            ) WROW
                            WHERE WROW.NoteId = @StartOffset)

    SELECT *
    FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY Created) RNUM, *
            FROM Notes
            WHERE BookId = @BookId AND Username = @Username
    ) WROW
    WHERE WROW.RNUM > @StartOffsetRow AND WROW.RNUM <= @StartOffsetRow + @Count

This setup seems to work, but contains repeat queries and a lot of select statements. Looking at the execution plan, there are 7 queries. I was wondering if there is a different approach to this problem with fewer calls. I am using SQL 2008 R2 server.

Quick and dirty example for smaller data sets.  For larger data sets I definitely recommend checking out the link Aaron Bertrand provided: http://www.sqlservercentral.com/articles/paging/69892/

declare @pagewidth int, @pagenumber int

select *
from
    (
        select
            *,
            row_number() over (order by PAGINGCOLUMN/S) as cnt
        from datatable
    )a
where cnt > (@pagenumber - 1) * @pagewidth
    and cnt <= @pagenumber * @pagewidth


Is this partial dependency? I have this relation:

R = ABCDE
F: AB->DE, AC->B, DE->C, AE->C, C->AB.

Is AC->B a partial dependency in F?

I'm guessing it is, because

C->AB can be decomposed to

C->A
C->B

from where we can conclude that there exists a subset of AC (C in our case) which goes into B, that is

C->B.

I couldn't find a formal definition on the internet, so I would like it if someone could confirm my assumption.

In order for AC->B to be a partial key dependency, it has to satisfy these conditions.

AC must be a candidate key. (Not necessarily the primary key.)
One of these functional dependencies must hold.

A->B, or
C->B

So the first question I'd ask myself is, "Is AC a candidate key of F?"


SQL Server transaction replication ignores DML which does not change data I have a transactional replication setup between the two servers and I noticed that if a run a statement similar to this:

UPDATE mytable
SET mycolumn = mycolumn

the replication somehow knows to ignore this transaction and it does not get applied on the subscriber. I have confirmed it by running SQL Profiler and also by adding TIMESTAMP column to my subscriber table (it does not change).  I suspect there is some sort of mechanism, which enables this kind of "smart" behavior and I was wondering if anybody could shed some light on it.

Thank you!

Tranactional Replication works by reading the transaction log.

This kind of "non updating update" often won't generate any transaction log records.

See Paul White's article The Impact of Non-Updating Updates for more about this.


DatetimeFieldOverflow when inserting more than three decimals precision TIMESTAMP type should be fine (docs)

  timestamp [ (p) ] [ without time zone ]   8 bytes     both date and time (no time zone)   1 microsecond / 14 digits

Create the table like:

CREATE TABLE IF NOT EXISTS l12(
    timestamp TIMESTAMP(6) NOT NULL,

But what I get is

psycopg2.errors.DatetimeFieldOverflow: date/time field value out of range: "2019-05-23 12:16:50.954276"

When I remove the last three numbers 2019-05-23 12:16:50.954276--> 2019-05-23 12:16:50.954 the insert works.

So is it possible to save a datetime with six decimals precision into one postgresql field?

Thanks to Akina, I found the problem. The error has been on client-side, because I used the following in the INSERT function:

INSERT INTO l12 VALUES (TO_TIMESTAMP('2019-05-23 12:16:50.954276', 'YYYY-MM-DD HH24:MI:SS.ms'))

which fails with the mentioned ERROR:  date/time field value out of range: "2019-05-23 12:16:50.954276"

Just remove that TO_TIMESTAMP function and be happy or use 'YYYY-MM-DD HH24:MI:SS.msus as the correct formating input.


Using column alias in a WHERE clause doesn't work Given a table users with two fields: id and email.

select id, email as electronic_mail
from (
        select id, email
        from users
) t
where electronic_mail = ''

Postgres complains that:

ERROR:  column "electronic_mail" does not exist

The example is just to demonstrate the arising problem. My actual case is more complex, I iterate though an array of elements in a json column, taking a single scalar value from each. (I can share some code if that helps.)

I really don't get what would be the complication, probably I'm unaware of something. I was under the impression that aliased columns can be employed in a WHERE clause without problem?

The manual clarifies here:

  An output column's name can be used to refer to the column's value in
  ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
  there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:

PostgreSQL: How to return rows with respect to a found row (relative results)?

Best to avoid column aliases that conflict with input column names a priori.

Aside: the subquery in your example is just noise since the WHERE clause is part of the outer query, so the example can be simplified to:

select id, email as electronic_mail
from users t
where electronic_mail = '';  -- doesn't work


PostgreSQL: windowing to replace multiple sub-queries ... hopefully I have two tables:

CREATE TABLE fund_data
(
  id serial NOT NULL,
  fund_entries_id integer NOT NULL,
  fund_val numeric(25,6) NOT NULL,
  bbg_pulls_id integer NOT NULL,
  CONSTRAINT fund_data_pkey PRIMARY KEY (id),
  CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
      REFERENCES bbg_pulls (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
      REFERENCES fund_entries (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id)
)

And:

CREATE TABLE fund_entries
(
  id serial NOT NULL,
  fiscal_prd_end_date date NOT NULL,
  company_id integer NOT NULL,
  ern_dt_id integer NOT NULL,
  CONSTRAINT fund_entries_pkey PRIMARY KEY (id),
  CONSTRAINT fund_entries_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES company (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_ern_dt_id_fkey11 FOREIGN KEY (ern_dt_id)
      REFERENCES ern_dt (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date),
  CONSTRAINT fund_entries_ern_dt_id_key UNIQUE (ern_dt_id)
)

To get the most recent value from fund_data for a given company_id and bbg_pulls_id, I currently do two correlated sub-queries:

SELECT fd.fund_val
FROM fund_data fd
    ,(
        SELECT fe.id
        FROM fund_entries fe
            ,(
                SELECT max(fe.fiscal_prd_end_date)
                FROM fund_entries fe
                WHERE fe.company_id = 858
                ) md
        WHERE fe.fiscal_prd_end_date = md.max
            AND fe.company_id = 858
        ) mi
WHERE fd.bbg_pulls_id = 20
    AND fd.fund_entries_id = mi.id

Question: The query works find, but can I use windowing to avoid the two sub-queries? I've tried several times and I keep getting multiple rows.

EDIT: A description of the query in words: Find the max date for a given company_id, so I can find the id for that record. Then use that id to get the most recent value associated with that fk id (and max date) for a given bbg_pulls_id and company. Hopefully that helps.

If I understand correctly:

SELECT DISTINCT
     last_value(fd.fund_val) OVER (ORDER BY fe.fiscal_prd_end_date)
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858

UPDATED:

Or just:

SELECT
     fd.fund_val
FROM
     fund_data AS fd
     JOIN fund_entries AS fe ON (fd.fund_entries_id = fe.id)
WHERE
     fd.bbg_pulls_id = 20
     AND fe.company_id = 858
ORDER BY fe.fiscal_prd_end_date DESC
LIMIT 1


Advanced design question related to compound primary keys As a database design expert, I had a discussion with an academic person about database design and I need some arguments on why some design is bad or not normalized or bad practice. As we cannot discuss here or have opinions, I'm just asking why something is wrong. A possible answer would be to show a clear violation of normalization rules or a pointer to a commonly accepted design standard. Or even an answer that this cannot be decided based on clear rules.

So, let's assume we have three tables. First is a Client table with values C1 and C2. Then we have a Users table with users A, B, C, D, and users A and B belong to Client C1, while users C and D belong to Client C2, so the Users table has a foreign key to the Client table. Then we have a Products table with values 1, 2, 3. Now we need to connect Products with Users (n:n), so we need a connection table.

If we list all possible values for the planned connection table, we get (Product, User, Client the user belongs to):

1 A C1
2 A C1
3 A C1
1 B C1 *
2 B C1 *
3 B C1 *
1 C C2
2 C C2
3 C C2
1 D C2 *
2 D C2 *
3 D C2 *

In my experience, where I lack of proof, every table should have a single primary key, not related to any content. The (wrong) design I was confronted with, made the connection table to not only have the Product-ID and User-ID, but additionally store the redundant field Client-ID (which could be looked up in Users table). Argument was that this is the only way to ensure that products are not connected twice to users of the same client (the values with the star in the above list would not be allowed). This can be done by adding a unique key on the connection table fields Product-ID and Client-ID, but it requires to have the Client-ID be present in that table, although it is already defined in the Users table and could be looked up there.

So why is this wrong? Only because of the redundant data (normalization rule)? How can we otherwise ensure then that no duplicates are stored (only by application logic or triggers)?

In a different scenario (but same underlying question), let's assume we have a grid with data, like an Excel sheet. To store this data we would have a table with the data per cell and foreign keys to the columns/rows table. The columns and rows are not different, so both columns and rows are stored in the same lookup table. This ColumnRow table had in the bad design a compound primary key (two fields), so the foreign key in the grid table would need both fields for each foreign key, requiring a total of 4 fields to reference the row and column. Now there's a restriction that the first part of the two foreign keys must be identical for both row and column, so only the "common" first part was stored ensuring this. If I design this with normal (single, not compound) foreign keys, we cannot ensure that both row and column belong to the same higher-level thing. So this makes it even more difficult for me to argue for single primary keys.

So in general, is there no generic consent to have single (non-compound) and data-unrelated primary keys? Or is this all just opinion-based? Is there expert (reference) literature about this subject that I could point to?

This is opinion based, or at least quite subjective in the real world with the best solution varying depending on a number of factors the relative importance of each different people with disagree on, unfortunately.

The question is whether you should ever create a surrogate key when there is an apparently perfectly suitable candidate key (compound or not) in the existing data. http://en.wikipedia.org/wiki/Surrogate_key has a fuller run down on the advantages and disadvantages, but I tend to err on the side of surrogate keys (integers or UUIDs depending on circumstance) in such circumstances for the following reasons:

Storage efficiency: The PK values are going to be found in all tables that refer to it in an FK, and in any indexes defined over those FKs: a surrogate key is likely to be smaller (at 4 bytes for a 32-bit integer, 8 (64 bits) if you need a larger range than that, 16 for a UUID) than any compound candidate key.
Data Consistency and Changes: a primary key should be unique, immutable, and always known, but many of the things people often assume fit those rules actually don't: people can change names, due to mistakes elsewhere there are people with duplicate identification numbers (such as National Insurance Numbers in the UK (note: you should not use NINos as a key for other reasons too)) so at some point the affected people might have that identifying number changed, and so on. ON UPDATE CASCADE or equivalent can help a lot in the changes scenario but that is not supported by all DMBSs (and can be very expensive depending on your data size).
Later Design Changes: No matter how immutable your client says the data and the rules around it are, they will at some point need to change that. Changes to the assumptions upon which you have chosen a natural candidate key to be the primary key for a table can have a much more significant effect on your design than if you had used a surrogate key.
Consistency in design/coding style, which can help understanding and maintainability later.

This is not hard and fast: there are circumstances where I'll prefer a natural candidate key over a surrogate key.

One thing you must remember when talking to academics is that they are usually talking from the point of view of having a perfect implementation of the theory they are talking about as a starting point, and there is absolutely no relational database implementations out there that can be considered to be that so there are practical considerations that an academic discussion may completely ignore due to this sort of "perfect world" assumption (in theory there is no difference between theory and practise, but in practise there usually is). I don't intend this to be disparaging to the academics here: I'm just meaning that we need to be very careful to define the context of such discussion to avoid talking at crossed purposes with us both being completely correct within the confines of the context we think we are talking about but quite wrong within the context the other party is framing the discussion in.


Dropped database still shows but causes errors PostgreSQL I had dropped a database a couple weeks ago but it still shows with \l.  I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was still showing up with \l.  Now when I try that command again I get:
template1=# \l

template1=# drop database mixmobi_master;
ERROR:  database "mixmobi_master" does not exist

template1=# \l
                                 List of databases
      Name           | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------
biterator_development   | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
mixmobi_master          | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
plantjotter_development | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres                | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0               | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/atmos         +
                        |       |          |             |             | atmos=CTc/atmos
template1               | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/atmos         +
                        |       |          |             |             | atmos=CTc/atmos
(6 rows)

Every couple of minutes or so it will also give me this error message:
FATAL:  database "mixmobi_master" does not exist
DETAIL:  It seems to have just been dropped or renamed.

Anyone have suggestions?  I'm running Lion and had used Atmos' Cinderella environment.

Edit

I used @Erwin's suggestion and got this:
template1=# SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

datid |    datname     | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts |          stats_reset
-------+----------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+-------------------------------
16387 | mixmobi_master |           0 |           9 |          2118 |       262 |     1826 |         2181 |        1157 |            0 |           0 |           0 |         0 | 2012-01-24 11:47:34.692079-06
(1 row)

template1=# SELECT pg_terminate_backend(procpid)
template1-# FROM   pg_stat_activity
template1-# WHERE  datname = 'mixmobi_master';
pg_terminate_backend
----------------------
(0 rows)

Does anyone else have any suggestions?  I'm still getting these errors and nothing has worked so far!  Thanks in advance for your time!

I would guess you had open connections / transactions while you dropped the db initially and the DROP is stalled somehow.

You can get more info on databases with statistics views. For instance:

SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

In case you find open backend processes, you might want to shut them down like this:

SELECT pg_terminate_backend(procpid)
FROM   pg_stat_activity
WHERE  datname = 'mixmobi_master';

More about that in the manual here.

Find a safe method to drop a database under load at this related answer:

Force drop db while others may be connected


How can I speed-up my query on geo-location processes I have a table that contains 10,301,390 GPS records, cities, countries and IP address blocks. I have user's current location with latitude and longitude. I created this query:

SELECT
  *, point(45.1013021, 46.3021011) <@> point(latitude, longitude) :: point AS distance
FROM
  locs
WHERE
  (
    point(45.1013021, 46.3021011) <@> point(latitude, longitude)
  ) < 10 -- radius
ORDER BY
  distance LIMIT 1;

This query successfully gave me what I want, but it is slow. It took 2 to 3 seconds to get one record by given latitude and longitude.

I tried a B-Tree index on the latitude and longitude columns, also tried GIST( point(latitude, longitude)); but still querying is slow.

How can I speed up this query?

Update:

It seems slowness is caused by the ORDER BY but I want to get the shortest distance, so the question remains.

You may consider using a GIST index based on using the function ll_to_earth. This index will allow for fast "nearby" searches.

CREATE INDEX
   ON locs USING gist (ll_to_earth(lat, lng));

Once you have this index, your query should be done in a different way.

Your (lat, lng) pairs need to be converted to the earth type, and compared with the indexed values (which are of the same type). Your query will need to have two conditions, one for "approximate" result, and one for the "precise" one. The first one will be able to use the previous index:

SELECT
    *
FROM
    locs
WHERE
    /* First condition allows to search for points at an approximate distance:
       a distance computed using a 'box', instead of a 'circumference'.
       This first condition will use the index.
       (45.1013021, 46.3021011) = (lat, lng) of search center.
       25000 = search radius (in m)
    */
    earth_box(ll_to_earth(45.1013021, 46.3021011), 25000) @> ll_to_earth(lat, lng)

    /* This second condition (which is slower) will "refine"
       the previous search, to include only the points within the
       circumference.
    */
    AND earth_distance(ll_to_earth(45.1013021, 46.3021011),
             ll_to_earth(lat, lng)) < 25000 ;

For using this code, you need two extensions (included in most PostgreSQL distributions):

CREATE EXTENSION IF NOT EXISTS cube ;
CREATE EXTENSION IF NOT EXISTS earthdistance;

This is the documentation for them:

Cube. You should take a look at the description of the @> operator. This module is needed by the next one.
EarthDistance. You will find here information about earth_box and earth_distance. This module assumes that the earth is spherical, which is an approximation good enough for the majority of applications.

A test with a table consisting of 2.2 million rows taken from the Free World Cities Database gives me the following answer to the previous query (which is not exactly the same as yours):

"ru","andra-ata","Andra-Ata","24",,44.9509,46.3327
"ru","andratinskiy","Andratinskiy","24",,44.9509,46.3327
"ru","chernozemelskaya","Chernozemelskaya","24",,44.9821,46.0622
"ru","gayduk","Gayduk","24",,44.9578,46.5244
"ru","imeni beriya","Imeni Beriya","24",,45.0208,46.3906
"ru","imeni kirova","Imeni Kirova","24",,45.2836,46.4847
"ru","kumskiy","Kumskiy","24",,44.9821,46.0622
"ru","kumskoy","Kumskoy","24",,44.9821,46.0622
"ru","lopas","Lopas","17",,44.937,46.1833
"ru","pyatogo dekabrya","Pyatogo Dekabrya","24",,45.1858,46.1656
"ru","svetlyy erek","Svetlyy Erek","24",,45.0079,46.4408
"ru","ulan tuk","Ulan Tuk","24",,45.1542,46.1097

To have an "order of magnitude" idea about timings: pgAdmin III is telling me that the time to get this answer is 22 ms. (PostgreSQL 9.6.1 with "out-of-the-box" parameters, on a Mac with Mac OS 10.12, Core i7, SSD)


Permission on Database I have a login user called TestUser created at the server level that is assigned to a read only role, now I need to create a user for the TestUser login on MyDb

USE [MyDB]
GO
CREATE USER [Test] FOR LOGIN [TestUser]
GO
GRANT SELECT TO [Test]
GO
DENY DELETE TO [Test]
GO
DENY INSERT TO [Test]
GO
DENY UPDATE TO [Test]
GO

everything is good but when I script that Test user created, I don't see in any place the script that I'm giving select and denying insert, delete and update

Most probably because you are using the generate scripts method to do so.

I would suggest to use open source DBA tools to view the permission or export.

Aliases : Export-SqlUser

Synopsis

Exports users creation and its permissions to a T-SQL file or host.

Description

Exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions.

Example:

  PS C:> Export-DbaUser -SqlInstance sqlserver2014a -User User1, User2
  -Path C:\temp\users.sql

Exports ONLY users User1 and User2 from sqlserver2014a to the file C:\temp\users.sql


Aren't two writes required to update a clustered index record I was reading the article on indexes at simple-talk, where it is written that

  If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have to occur. One write for inserting the row, and one write for updating the non-clustered index. On the other hand, if a table has a clustered index as the primary key, inserts take only one write, not two writes. This is because a clustered index, and its data, are one in the same. Because of this, it is faster to insert rows into a table with a clustered index as the primary key than it is to insert the same data into a heap that has a non-clustered index as its primary key. This is true whether or not the primary key is monotonically increasing or not.

Isn't it wrong? Clustered indexed naturally resort to B+ trees, where only the keys are stored in intermediate nodes (unlike B-trees, where the entire record is stored. That is why B+ trees can fit more keys in a single page, resulting it being bulky in width but shorter in height), so all records are stored in the leaf pages (the pages themselves being logically sorted through linked-lists, while the data in each page being physically sorted). So if a record has to be updated, say the value 1 has to be updated to 7, won't the update need to be applied to both the key in the clustered index top node (this may, in cases, cause a re-structuring of the entire structure) and the corresponding value in the record in the leaf-page?

Update: Okay, I did some study, and found that apart from the initial tree structure (where some values must be present twice, for example the key values in the node), when new values are inserted, they just fit into the leaf page, whereas the tree gets restructured to accommodate that. However, when, say 5 values are inserted, the 3rd value may cause the first inserted value (which is currently occupying only leaf level space) to cascade up, thus causing it to be written twice (once in the leaf level, other in the index level). Of course, such re-writes (although they don't occur at the time of insertion, they can occur later) will be much less compared to the two-time write which occurs each time there is an insertion into a heap with NCI,  but still isn't it wrong to say that there is no re-write?

I think the problem here is a difference in terminology.

The "number of writes" that's usually referred to is the number of object accesses, rather than the number of pages that get touched by the physical operation.

The reason why that's usually used as a metric in discussion is because it's a more "stable" and meaningful number to talk about. As we're getting into here, the number of pages touched by an INSERT statement for even a single row depends on many factors, so it's not a very useful quantity outside your own environment and situation.

The one thing I would pick at from the article quote is this (emphasis mine):

  One write for inserting the row, and one write for updating the non-clustered index.

This may be confusing. Inserting a row into the base table would involve an insert to the base table, and also an insert into each nonclustered index (ignoring special index features), not an update.

  So if a record has to be updated, say the value 1 has to be updated to 7, won't the update need to be applied to both the key in the clustered index top node (this may, in cases, cause a re-structuring of the entire structure) and the corresponding value in the record in the leaf-page?

Yes, assuming the column that was updated is in the index key. However, this is still a single object access, and hence a "single write."


Should I move to InnoDB during a planned migration? I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick with my usual rule of thumb that I stay with as standard a Debian install as possible for maximum stability, particularly as enabling InnoDB doesn't seem to be completely fool-proof

The transactional load on the database is minimal as it is a low traffic website. I'm more concerned with keeping management as simple as possible than anything else. The site runs a customised version of WordPress developed by a 3rd party.

I should add that my experience with MySQL in any form is minimal.

Should I stick with my plan or take the opportunity to look into InnoDB?

To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable.

MyISAM is probably easier to manage from a DBA standpoint with recovery and repair.

And in 5.1, I think InnoDB's benchmarks aren't really up to par. I could be wrong on this, so I'm going to look it up again!

Edit: Quick google turned up this article in 2009 using 5.1 http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/

Gives some nice talking points, specifically the section on InnoDB needing tuning.

HTH


Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc.

There are a number of functions out there but the ones I found all have limitations, e.g. truncate the string to 8000 characters. e.g. (according to some of the comments):

SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

One of the comments proposed a better solution but the - 1 causes an incorrect syntax error and I am not sure why.

CREATE FUNCTION dbo.SuperTrimLeft(@str varchar(MAX)) RETURNS varchar(MAX)
    AS
    BEGIN
    IF (ASCII(LEFT(@str, 1)) < 33) BEGIN
        SET @str = STUFF(@str, 1, PATINDEX('%[^'+CHAR(0)+'-'+CHAR(32)+']%', @str) – 1, ' ');
    END;

    RETURN @str;
END;

So my question is, what is the best approach to accomplish the task above?

The best way would probably be to create a CLR function and use .NET framework libraries.

A TSQL attempt is below.

DECLARE @Pattern NVARCHAR (50) = N'%[^' +
/*List from http://stackoverflow.com/a/18169122/73226 */
NCHAR(0x0009) +  /*tab*/
NCHAR(0x000A) +  /*line feed*/
NCHAR(0x000B) +  /*line tabulation*/
NCHAR(0x000C) +  /*form feed*/
NCHAR(0x000D) +  /*carriage return*/
/*Various spaces from https://www.cs.tut.fi/~jkorpela/chars/spaces.html */
NCHAR(0x0020) +
NCHAR(0x00A0) +
NCHAR(0x1680) +
NCHAR(0x180E) +
NCHAR(0x2000) +
NCHAR(0x2001) +
NCHAR(0x2002) +
NCHAR(0x2003) +
NCHAR(0x2004) +
NCHAR(0x2005) +
NCHAR(0x2006) +
NCHAR(0x2007) +
NCHAR(0x2008) +
NCHAR(0x2009) +
NCHAR(0x200A) +
NCHAR(0x200B) +
NCHAR(0x202F) +
NCHAR(0x205F) +
NCHAR(0x3000) +
NCHAR(0xFEFF) +
N']%' COLLATE Latin1_General_100_BIN2;

WITH T(String) AS
(
SELECT '         leading only' union all
SELECT '

Line breaks

' union all
SELECT '    tab and space    ' union all
SELECT ''
)
SELECT '[' + SUBSTRING(String,Start,len(String + '..') - Start - Finish) + ']'
FROM T
CROSS APPLY
(
SELECT  PATINDEX(@Pattern,String),
        PATINDEX(@Pattern,REVERSE(String))
)ca(Start, Finish)

The [ and ] delimiters are applied just to make it easier to verify there is no white space lurking there. Remove these when you're happy this is the case.


SQL Replication Commands I'm trying to figure out how i can get the number of commands over the last day for a sql server replication. Transactional / Snapshot / Merge.

I really want to be able to calculate the average number of new sql commands per min and latency per min but i should be able to calculate that if i can understand how to identify new commands for a replication from its history.

i have  query that i have shown below but i'm not sure if i'm on the right path.

select
his.delivered_commands as [NewCommands],
his.time as [time],
his.comments,
HIS.*
from distribution..MSdistribution_history his
inner join distribution.dbo.MSdistribution_agents mda on his.agent_id = mda.id
where mda.subscriber_db<>'virtual' and mda.publication = 'SomeReplication'
order by his.time desc

Can anyone please help on this one perhaps as i'm completely stumped.

I am using SQL Server 2008 R2.

Iffy.

There are perfmon counters that you can probably use for what you're trying to do. Since it's a counter provided by SQL, it's available in sys.dm_os_performance_counters. Try select * from sys.dm_os_performance_counters where object_name like '%repl%'. Slice and dice as necessary.


How to use OpenLDAP for Oracle database user authentication In our enterprise, we have a large number of Oracle databases and (obviously) large number of users with access to different databases. We would like to ease the procedure to create/drop/modify users on an enterprise level. A LDAP solution comes to mind but the solutions from Oracle Corp are too expensive. Needless to say, my mgmt asked me to research and come up with an alternative. I read about OpenLDAP. My questions:

How do I setup OpenLDAP for oracle user security authentication ?
Can I use OpenLDAP as a standalone or does it have to be integrated with any Oracle product like OID, OVD, ODSEE etc?
Are there are any certified install procedures for setting up OpenLDAP w/ Oracle database?

I didn't find anything on the internet for setting up OpenLDAP. Any help/pointer would be appreciated. The goal is to avoid any products from Oracle Corp purely for cost reasons.

Thanks

Permitted Features, Options, and Management Packs by Oracle Database Offering

  Enterprise User Security is a feature for directory-based management
  of database users. It requires a corresponding Oracle Identity
  Management Directory Services Plus to be licensed.

Enterprise User Security: The Big Picture

  Enterprise User Security requires Oracle Internet Directory 10g
  (9.0.4) or higher. Other LDAP-compliant directory services are
  supported by using Oracle Internet Directory Integration Platform to
  synchronize them with Oracle Internet Directory. Another directory
  services product, Oracle Virtual Directory, provides a single, dynamic
  access point to multiple data sources through LDAP or XML protocols.
  Oracle Virtual Directory can provide multiple application-specific
  views of identity data stored in, for example, Oracle Internet
  Directory, Microsoft Active Directory and Sun Java Systems Directory
  instances, and can also be used to secure data access to the
  application-specific sources and enhance high-availability to existing
  data-sources.

Through Oracle Virtual Directory or Oracle Unified Directory.
See above. Technically it should be possible to use as standalone, but not supported at all.
None for direct connection. See above.


What is the difference between TABLESAMPLE(10) and TABLESAMPLE(10 PERCENT) in SQL SERVER 2012 My understanding of the use of TABLESAMPLE is that it returns the top n percent of rows in a result set where TABLESAMPLE(n)

So I wrote the following queries to just check (as I'm learning T-SQL)

SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person TABLESAMPLE(10)

SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person TABLESAMPLE(10 PERCENT)

Each query returns a different number of rows every time I execute it.

So I obviously don't understand TABLESAMPLE.  Could somebody explain?

The short answer is that TABLESAMPLE is not guaranteed to return the number of rows you specify (or the %) because it bases this on pages and not on rows, and returns all of the rows from the pages it identifies. Unless your table only has fixed width columns, the pages pulled out based on % or number of rows could contain vastly different numbers of rows.

You can make this more consistent using the REPEATABLE option, but that still won't make it return the right number of rows. If you are simply trying to retrieve an exact number or percentage of random rows, use:

SELECT TOP (10) -- or 10 PERCENT
  columns
FROM dbo.table
ORDER BY NEWID();

Please read through this tip and this question and all the answers.


Storing revisions and relations between revised items in 1:N and N:N scenarios for audits I have several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution.

1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both need to have revisions. The data sets stored tend to be get pretty big for some of these tables and, according to the business rules I gathered, there will be many changes that need auditing so copying all the child items for each revision of the parent will not scale very well I predict.
N:N related tables SO and PO tables (where I use a pivot table of course) that needs to be audited also so that changes from at least one way imply (or may imply) storing an updated revision of the data entry form the other table. (We need no worry about the child _items data entries for each of these tables because although they may be related, I don't require auditing for now).

My strategy for now is to use a separate table suffixed with _revision for each of the above tables and store the current revision number in the normal table and a revision number for each revision, at least for the parents.

So my question are:

is my schema flawed in some major way that I don't predict for this kind of scenario? (I suppose there are many other applications that have solved this problem out there).
should I make a direct relationship between x_revison and y_revision or use the revision number and query through the normal (current revision?) table? I want the variant that reduces complexity the most and I'm pretty sure querying through is simpler on most operations (I suspect there will not be a big disparity between SELECT/INSERT/UPDATE in normal operation, so I don't need to optimise for one type of DB operation). Am I missing something?
is it common practice to copy-on-write the revisions or should I just lazily insert entries in the revisions table when a change appears only? Any possible pros/cons to these two options.

You have tradeoffs to consider when deciding on the design.  Which do you want to optimize for:

Insert new item
Revise item
Fetch "current" item
Fetch some old revision
Fetch all revisions

If one of those actions dominates, then design for it, and kludge for the rest.

Also, do revisions need extra columns, such as revisor, revision_date, revision_number, ...?  This may 'force' you to have a separate table for revisions.

If not using a separate table, consider this design:

Have a column rev for revision number:  0 for "current"; 1...N for old versions (1 is oldest).  Then it takes a couple of statements to move 0 to N+1 and insert a new 0.  The Fetches are pretty obvious.  Include an composite index (the PRIMARY KEY?) in this order: (rev, item_id).


MongoDB multi-datacenter deployment and shard keys I am trying to check my understanding after reading docs and some older questions, so my question is quite loaded:

In a sharded cluster, each shard should (and as of 3.6 must?) reside on its own replica set. 2) Each replica set has its own master.

So suppose I wanted to deploy a Mongo cluster in 2 availability zones. So I set up a 6-node cluster with 2 shards on 2 separate replica sets, such that master for each set is in different datacenter. Slave nodes are distributed such that at least one slave from each replica set is in each datacenter, so:

If I use "majority" read concern, would my mongod instances within same replica set have to communicate between my datacenters to verify that data is available on at least 2 instances? It sounds a little silly. Or would replication take longer, as each instance needs to confirm to other instances that its write is complete?
If I use "available" read concern, can I guarantee that my application reads from closest instance? Can I make my application (say Mongo CLI) prefer a certain slave?
If I wanted to guarantee each write happens to the closest master, I could use region as shard key.

Now suppose I wanted to do same exercise with 4 shards, 2 on each replica set.

Is it possible to have more than one shard in one replica set? Ie each node would duplicate 2 sets of data?
If so, and I wanted to still make sure each write happens to the closest master, I would use a compound shard key, with region and some other attribute. Can I somehow configure ranges of my shard key hash results to each master? Ie I'd like to achieve something like:

Shard 1 and 2 reside in US-West datacenter and shard 3 and 4 reside in US-East datacenter
Then for my compound shard key of (region, foo) I would want to guarantee that:
| region = US-West | region = US-East |
| foo < 0 | foo >=0| foo < 0 | foo >=0|
| shard 1 | shard 2| shard 3 | shard 4|

Yes, your question is quite loaded...
1.1 Yes, every shard should have RS
1.2 Every RS have it's own master.
2.1. Only if you do slave reads. Normally read is done from primary. It will not affect replication directly. Only write of data, when write concern is majority.
2.2. If you do slave read AND you connect to specific instance, then data is read from that instance.
2.3. Yes you can do that.
3.1. NO.
3.2. NO.
You can have (of course) 2 shards in US-West and 2 shards in US-East... You just have then 4 shard cluster what uses compound sharding key {region:1,foo:1}, but every shard have different data. You just need to set chunk limits by hand (to have that foo < 0 and foo >= 0) and disable balancing between shards... End result may not be the best available, because of uneven distribution of data.
Best possible sharding key distributes written data evenly to every shard, so there is no need for balancing between shards and queries never need to read from multiple shards. With "bad" key, you are writing all data to one shard and then balancing is moving that data to other shards (later).


Using a linked server with OPENQUERY in a database project I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only one error left:

In one view the devs used OPENQUERY to access a linked server. So I imported a DACPAC which contains the right database and added it to the project by using Add Database Reference using the following reference options.

Initial Script Version

Here is a shorter version of the original view creation:

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

This lead to the following error:

  Error 136 SQL71501: View: [dbo].[vwStatus] has an unresolved reference to object [LinkedServer].

First Attempt

So I tried to insert the server name variable

FROM       OPENQUERY($(LinkedServer), 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

Which leads to

  Error 176 SQL46010: Incorrect syntax near $(LinkedServer).

Further Attempts

I fiddled arround a bit and tried the following (with and without having quoted identifiers enabled):

FROM       OPENQUERY("$(LinkedServer)", 'SELECT * FROM [DB].[dbo].tbStatus') AS
FROM       OPENQUERY([$(LinkedServer)], 'SELECT * FROM [DB].[dbo].tbStatus') AS
FROM       OPENQUERY([LinkedServer], 'SELECT * FROM [DB].[dbo].tbStatus') AS
FROM       OPENQUERY("LinkedServer", 'SELECT * FROM [DB].[dbo].tbStatus') AS

I am always getting an error.

I have no clue what I'm overlooking here. Do you? Thanks for your time!

(Sadly I can't add the visual-studio-2013 tag, so I used visual-studio)

I've managed to get it working:

I created a new database project master. In there I created a folder Server Object and a     file LinkedServer.sql. In the SQL file i added the linked server:

GO
EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

After adding the database Project master to my solution and referencing it in my original databse project, I was able to build the project using the initial syntax;

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1


inserting blob/clob over dblinks I'm trying to find the best way to load a remote table with a blob column (blob only contains text, so clob would have been better).
So far we've been able to reduce loadtime from 218 minutes to 2 minutes using a view on the source table and converting the blob to varchar2 using (simplified):
CREATE OR REPLACE FORCE EDITIONABLE VIEW "TABLE_V" ("ID", "DATE", "CLOB_PT1", "CLOB_PT2", "CLOB", CONSTRAINT "TABLE_V_PK" PRIMARY KEY ("ID") RELY DISABLE) AS
  SELECT
  "ID",
  "DATE",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 1)) "CLOB_PT1",
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOB, 2000, 2001)) "CLOB_PT2",
  CASE WHEN LENGTH(INHOUD) > 4000 THEN TO_CLOB(BLOB) END "CLOB"
  FROM OWNER.TABLE;

Because we cannot guarantee the size of the blob will continue to fit in our two varchars we put anything bigger into a separate clob column in the view.
now the idea is to merge the varchars or clob back to one clob in our staging area.
Here come's the challenge of ORA-22992: cannot use LOB locators selected from remote tables
Concatenating the two varchars into the targeted clob column works fine.
INSERT
  /*+  APPEND PARALLEL  */
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  )
SELECT
  TABLE_V_1.ID ,
  TABLE_V_1.DATE ,
  TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
FROM
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

Inserting the clob into the targeted clob column works fine.
INSERT
  /*+  APPEND PARALLEL  */
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  )
SELECT
  TABLE_V_1.ID ,
  TABLE_V_1.DATE ,
  CLOB
FROM
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

Using a 'case when' inserting either the concatenated varchars or the clob column fails!
INSERT
  /*+  APPEND PARALLEL  */
  INTO SA.TESTTABLE
  (
    ID ,
    DATE ,
    CLOB
  )
SELECT
  TABLE_V_1.ID ,
  TABLE_V_1.DATE ,
  CASE WHEN CLOB IS NOT NULL
    THEN TABLE_V_1.CLOB_PT1||TABLE_V_1.CLOB_PT2
    ELSE TABLE_V_1.INHOUD_CLOB
  END
FROM
  OWNER.TABLE_V_1@DB_LNK TABLE_V_1

I also tried concatenating all three columns by default, (varchars1 and 2 will be empty if size blob >4000), same error.
Why is this failing?
Any suggestions on getting this to work?

To insert lobs over a database link, your inserts need to be in the form:
INSERT INTO table1@remote_site SELECT * FROM local_table;
http://docs.oracle.com/database/121/ADLOB/adlob_working.htm#ADLOB45148
You can do this by:

Creating a temporary table
Loading your clob data into the temp table
The insert over the DB link with "insert@link .. select"

For example:
create table t (
id int,
x clob
);
create global temporary table gtt (
id int,
x clob
) on commit preserve rows;
declare
l clob;
begin
insert into gtt values ( 1, empty_clob() )
returning x into l;
for i in 1 .. 10 loop
dbms_lob.writeappend(l, 4000, lpad('x', 4000, 'x'));
end loop;
commit;
insert into t@loopback
select * from gtt;
end;
/
select id, length(x) from t;
    ID  LENGTH(X)

     1      40000

(Loopback is a DB link pointing back to the same database)


How do I use RMAN to create a test database from a full recovery backup dataset? In plain english for a someone whose DBA role is a third or seventh hat, and rarely has to touch Oracle, how do I use RMAN to create a test database from a full recovery backup dataset?

I've kept my whining at the end of the question ...

Scenario:

I have a complete RMAN backup set of a production database, taken as a copy of the flash recovery directory, from which I want to build a test database.

I have started to glean an understanding of what I need to do from this post: Oracle 11 rman db restore file corruption.

What I'm not getting is where some of the "alphabet soup" in his example script comes from.

Example script (with annotations):

rman target /
set DBID ...    // Where does this come from?  The existind DB I plan to overlay, or some magic spot in the RMAN backup set?
startup nomount
RESTORE CONTROLFILE FROM 'file destination' // is this my flash recovery directory?
shutdown;
startup mount;

// Is it right to assume that this is the directory containing the backup?
// Or should I list every single backup file from the flash recovery area (ugh!)?
catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';

rman:crosscheck backup; Run{
SET UNTIL SEQUENCE 50511;    // Where did this number come from?  Is it important?  Can I ignore this line since I have a complete recovery set?
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

sqlplus sys/sys as sysdba

shutdown immediate; startup mount;
alter database noarchivelog; // I see this is considered a bad practice elsewhere??

ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace // what's this line about?  I just want to take a clone of the DB that was backed up elsewhere
ADD TEMPFILE 'c:\oracle............dbf' SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Whining:

I know that this is yet another noob question, and some of you experienced oracle DBA's will be rolling your eyes and thinking "RTFM".  Unfortunately, the ease of management of SQL server is changing industry expectations, and anyone who can use a mouse is expected to be an instant DBA.  That works with SQLServer, but not with Oracle.

In SQL server this is a simple task that comes down to pick your source backupset, pick your target database, click on the "replace database from backup", click "go", and then go for coffee.

In Oracle with rman, it looks like it should also be a simple task, but all the documents I have found appear to assume that the oracle DBA role is filled by a full time specialist in oracle who just needs a nudge in the right direction.  While Tom Kite is really good, his page on this exact problem spends a lot of time babbling about transportable spaces and such, and quickly becomes indecipherable because of the assumptions of the depth of knowledge of the reader.

First place to check is Oracle official documentation.
For example http://docs.oracle.com/cd/E36909_01/backup.1111/e10642/rcmdupdb.htm#autoId0
Depending of your database version(10g, 11g, 12c...).
Just search documentation for duplicate database.
I am not going to explain all questions from your script because it could only confuse you a little more.

Depending of prod db size you could use datapump export and import for example, or transportable tablespaces or like you tried RMAN.
Your rman commands should be something like

-- scripts for restore using RMAN,
-- This script is usable when the oracle versions are the same,
-- or a direct upgrade is supported
--
-- prep database for rman import
rman target /
shutdown immediate
startup nomount
exit

-- duplicate the database
rman auxiliary /
duplicate database to newdb
  backup location 'E:\Backup\oracle\flash_recovery_area\olddb\BACKUPSET\2016_07_29' nofilenamecheck
  DB_FILE_NAME_CONVERT ('E:\app\oracle\oradata\olddb','E:\Oracle\oradata\newdb')
  LOGFILE
    'E:\Oracle\oradata\newdb\redo01.log' SIZE 2G,
    'E:\Oracle\oradata\newdb\redo02.log' SIZE 2G,
    'E:\Oracle\oradata\newdb\redo03.log' SIZE 2G;

duplicate database to testdb spfile backup location 'backup_path' nofilenamecheck;

When the databases are of different versions, use expdp/impdp:

-- Alternate procedure using using expdp / impdp
--
-- Export on source database
sqlplus: create or replace directory data_pump_directory as 'f:\dp';

cmd: expdp user/password@olddb schemas=SchemaToMigrate directory=data_pump_directory dumpfile=dbname.dmp logfile=expdp_dbname.log

-- generate script for generating user from old database
-- see http://www.oracle-scripts.net/generate-user-ddl/
--
-- copy output files from source system to destination system
--
-- Prep destination database for the import
-- use script generated above to create user/schema on destination database
sqlplus: create or replace directory data_pump_directory as 'f:\dp';

-- From bitter experience, this will not necessarily work with the user/password@newdb
-- on the command line.
-- If impdp chokes with ORA-39087:
-- 1. Confirm database is open (alter databaase open)
-- 2. Confirm the directory reference is created in the database
-- 3. Confirm the system and schema users are granted read and write on the directory reference
-- 4. Confirm the oracle service (OS) user has authority on the OS directory
-- 5. then try not supplying credentials until impdp asks for them (yeah,  a possible bug somewhere)
cmd: impdp user/password@newdb schemas=SchemaToMigrate directory=data_pump_directory dumpfile=dbname.dmp logfile=impdp_dbname.log table_exists_action=replace  remap_tablespace=oldts1:newts1,oldts2:newts2

This script can be written in many ways, depending if you want to recover to specific date and time, specific SCN, is test db the same structure(the same path for data and other files) or not, the same operating system or not, do you need to change some initialization parameters or not etc.


Retrieving SQL Server InfoMessages in PowerShell 5 The following PowerShell function worked correctly on PowerShell 2 but now I'm testing it on PowerShell 5 and can't make it work. It is meant to automate backup restore tests. But since SQL Server backup related tasks use messages instead of returning a recordset I had to add a add_InfoMessage with a handler.

I think I could do it with Invoke-Sqlcmd -Verbose but I prefer this method.

I've also searched how to do it with SMO but found nothing

function Restore-Test {
    param (
        [string[]] $bkpPath,
        [string[]] $testServer
    )
    $message = "Inicio: " + (get-date);
    $message += "`nArchivo: " + $bkpPath;
    $message += "`nTestServer: " + $testServer;
    $connString = "Server=" + $testServer + ";Database=master;Integrated Security=SSPI;";
    $commandString = "print 'Message from MSSQL!';";
    #$commandString = "DBA_TOOLS.dbo.sp_RestoreTest N'" + $bkpPath + "'";
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString;
    $conn.Open();
    $cmd = $conn.CreateCommand();
    $cmd.CommandText = $commandString ;
    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $message += "`n" + $event.Message };
    $conn.add_InfoMessage($handler);
    $conn.FireInfoMessageEventOnUserErrors = $true;
    $cmd.CommandTimeout = 0;
    $cmdResult = $cmd.ExecuteNonQuery();
    $conn.Close();
    $message += "`nExecution result: " + $cmdResult + "`nFinalizacion: " + (get-date);

    return $message;
}
export-modulemember -function Restore-Test

You will not be able to capture the print statements in the manner you are trying. The handler is meant to output the statement to the console, not to actually capture it.

This would cause the message to write the console, but you will never (at least my testing) be able to append this into your $message variable. Which I don't recall this working like you have it in PowerShell 2.0, but I don't have that version available anymore to try.

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };

Now why this has changed I have not found yet. If I ever do I will try to remember to come update this post.


Unpivot multiple similar columns I am trying to normalize a few tables. Here is one of my tables

 SalesID|Order1Name|Order1Date|Order1Amt|Order2Name|Order2Date|Order2Amt|Order3Name......Order10xxxx
 ---------------------------------------------------------------------------
1001     | first   | 1/1/18   | 111.00  |  second  | 2/1/18   | 222.00

I want to get it to

SalesID |OrderNum  |  OrderName  |  OrderDate  |  OrderAmt|
------------------------------------------------------------
 1001      1          first          1/1/18         111.00
 1001      2          second         2/1/18         222.00
 1001      .
 1001      .
 1001      10

I have been trying to run with unpivot - how do make sure I have the ordinality (column2) info?

I started with the information in Aaron Bertrand's awesome post Use SQL Server's UNPIVOT operator to dynamically normalize output.  You should read the information in that post as Aaron does a great job of explaining the process.

I had to make some changes to Aaron's process due the fact that you had more columns that what Aaron had in his example(s).

--demo setup
drop table if exists table1;
go
CREATE TABLE Table1
    (SalesID int, Order1Name varchar(10), Order1Date date, Order1Amt int, Order2Name varchar(10), Order2Date date, Order2Amt int)
;

INSERT INTO Table1
    (SalesID, Order1Name, Order1Date, Order1Amt, Order2Name, Order2Date, Order2Amt)
VALUES
    (1001, 'first', '2018-01-01', 111.00, 'second', '2018-02-01', 222.00)
;

SELECT
    SalesID,
    ROW_NUMBER() over(partition by SalesID order by OrderDate) as OrderNum,
    OrderName,
    OrderDate,
    OrderAmt
FROM
(
  SELECT SalesID, OrderName, OrderDate, OrderAmt,OrderNames,
    idon = replace(replace(OrderNames,'Order',''),'Name',''),
    idod = replace(replace(OrderDates,'Order',''),'Date',''),
    idoa = replace(replace(OrderAmts,'Order',''),'Amt','')
  FROM
  (
    SELECT SalesID, [Order1Name], [Order2Name], [Order1Date], [Order2Date], [Order1Amt], [Order2Amt]
    FROM dbo.Table1
  ) AS cp
  UNPIVOT
  (
    OrderName FOR OrderNames IN ( [Order1Name], [Order2Name])
  ) AS OrderName
  UNPIVOT
  (
    OrderDate FOR OrderDates IN ( [Order1Date], [Order2Date])
  ) AS OrderDate
  UNPIVOT
  (
    OrderAmt FOR OrderAmts IN ( [Order1Amt], [Order2Amt])
  ) AS OrderAmt
) AS x
WHERE idod = idon and idoa = idon;

| SalesID | OrderNum | OrderName | OrderDate  | OrderAmt |
|---------|----------|-----------|------------|----------|
| 1001    | 1        | first     | 2018-01-01 | 111      |
| 1001    | 2        | second    | 2018-02-01 | 222      |


Pagination - Text comparison with greater than and less than with DESC I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if you are sorting ASC or DESC. This works great for integers and dates but I am wondering how best to do it with text columns, specifically for the first page.

For example, for the first page when sorting by name it would be

SELECT *
FROM users
WHERE first_name > ''
ORDER BY first_name ASC
LIMIT 5;

Then the next page would be

SELECT *
FROM users
WHERE first_name > 'Caal'
ORDER BY first_name ASC
LIMIT 5;

This works great. I am unsure about DESC order though. This seems to work but I am unsure if it is 'correct'.

 SELECT     *
 FROM   users
 WHERE  last_name < 'ZZZ'
 ORDER BY last_name DESC
 LIMIT 5;

Second page

SELECT  *
FROM    users
WHERE   last_name < 'Smith'
ORDER BY last_name DESC
LIMIT 5;

P.S. I am using the jooq support for the seek method and prefer to not have to hack around the native support, so ideally there is a proper parameter to put in the 'ZZZ' place above. i.e. there WHERE part of the clause is mandatory.

I don't think just placing an arbitrary value would work, since you have to put a larger if not possibly the largest value.

Perhaps pass in a boolean value with an OR operand (WHERE someflag OR last_name < @key), than way you do not have to pick arbitrary lowest value and largest values.

Otherwise if you can only control what is passed and not the WHERE operation, you'll have to use whatever the max value is for the column based on encoding.


how to get total count of partial counts An extension to this Question here, how can I fetch the count for ALL the students who have reached their maximum allowed absents.

I tried this

SELECT COUNT(*) FROM
(
SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   configurations)
) abc

but it is not working and gives this error:

  No column was specified for column 2 of 'abc'.

Environment: SQL Server 2000

Try adding a column name for COUNT(attendance) - such as CountAttendance.

SELECT COUNT(*) FROM
(
SELECT student_id,
       COUNT(attendance) as CountAttendance
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   [configuration])
) a ;

Removing COUNT(attendance) from the SELECT clause of the subquery should also work (as you found out).

  But why a non-aliased COUNT column causes issue?

Because all columns in a subquery should be named/aliased. Some DBMS (and some versions) don't require this, as they provide arbitrary names themselves. Others, like SQL Server, are more restrictive.

You could even have a single column with SELECT 1 AS c in the subquery or SELECT NULL AS c. The outer query needs only to count rows with COUNT(*) so the column list in the subquery is completely irrelevant. What matter are the WHERE and HAVING clauses.


How to drop empty tables Is it possible to drop all empty tables from my huge database (mysql)?

I'm looking for a sql command to automatically remove all those empty tables.

Currently, I have 305 tables in my dataset, and about 30% of them are old empty tables, that will not be used in my new application.

Just to clarify; All tables are of type=MyISAM

The idea would probably be to look for the empty tables using INFORMATION_SCHEMA.TABLES

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS =  '0'
AND TABLE_SCHEMA = 'my_database_only'

Then you might be able to produce an SQL query with

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS = '0'
AND TABLE_SCHEMA = 'my_database_only';

Quick and a bit dirty, but should actually work.


Teradata ODBC - version 14 and 15 side by side Is there a way to install version 15.11 alongside version 14.10 of the ODBC Teradata drivers?

I've run the 15.11 install wizard but it seems to want to remove all previous versions automatically.

Ideally, when we upgrade a production server, we'd like to run both and have the option to run both and switch between them if the version 15 driver causes a fault.

With regards to your question the readme specifies the following:

  TTU_BASE.15.10.12.00.readme.txt

  This release is not compatible with any Pre-15.10 release and it has
  to be removed  before installing this release.

  This release can co-exist with 16.00 and later releases.

  The TTU installer will block the installation if a pre-15.10 release
  is installed. Options:

  Continue (remove the pre-15.10 release first and then install the 15.10 release)

  Cancel the install

  Please see the Installation Guide for details.
  (https://teradatanet0.teradata.com/Site00BA)

But why keep 2 versions on the same server? TTU is 4 versions backwards compatible and 2 versions upwards compatible, so you are reasonable safe.

The biggest question is: is the software that is using the TTU certified for use of the TTU 15.10 version? I.e. Informatica Powercenter 9.6.1 is certified with TTU 14.10, but is NOT certified with TTU 15.10. In fact: I've found a knowledge base article from the vendor that specifically recommended with an error situation to first downgrade as they were not providing support on an not support combination of software.

In our case Powercenter 9.6.1 talks to TTU 14.10 and will be talking to Teradata 15.10. This combination is certified/supported.


Subtract TotalExpense from BudgetAmount In Budget Table for a specific date I have a Budget table which has the following columns:

BudgetId, BudgetDate, BudgetAmount

and a table Expense which has the following columns:

ExpenseID, Date, ExDescrip, Category_fk, ExAMount

How can I subtract daily expense from the budget of a sepecific date?

I am using this query to get the sum of the expenses. This query is ok to give the total expense of that date from budget.

select sum(ExMount) as totalExpense
from Budget b, Expense ex
where b.BudgetDate=ex.Date

Now, how can I subtract totalExpense from the BudgetAmount from the budget table?

CREATE TABLE [dbo].[Budget]
( [BudgetId] [int] IDENTITY(1,1) NOT NULL,
  [BudgetDate] [date] NULL,
  [BudgetAmount] [int] NULL,
  CONSTRAINT [PK_Budget]
    PRIMARY KEY CLUSTERED ( [BudgetId] ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The script of Expense table is

CREATE TABLE [dbo].[Expense](
    [ExpenseID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NULL,
    [ExDescrip] [varchar](50) NULL,
    [Category_fk] [int] NOT NULL,
    [ExMount] [int] NULL,
    [BudgetFk] [int] NULL,
 CONSTRAINT [PK_Expense] PRIMARY KEY CLUSTERED
(
    [ExpenseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

To start with, JOIN and GROUP BY

select
    b.BudgetDate,
    sum(b.BudgetAmount) - coalesce(sum(ex.ExMount),0) as BudgetLessExpenses
from dbo.Budget b
left join dbo.Expense ex
on b.BudgetDate=ex.Date
group by b.BudgetDate

BTW. Shouldn't there be a UNIQUE constraint on [Budget].[BudgetDate] ?


SQL server changes XML structure when inserted I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server.
Here is the data I insert

              <xsl:value-of select="name/n/given" />
            <xsl:text> </xsl:text>
          <xsl:value-of select="name/n/family" />

When I read it back, it looks like this

              <xsl:value-of select="name/n/given" />
          <xsl:text />
          <xsl:value-of select="name/n/family" />

Pay attention to the second line. This is a problem because it changes how the XSLT transformation output will be. The first example will create a space between given and family name, while the second will not create any space, so it will be like JohnJohnsen, while the first one will be like John Johnsen.

Is there some way to solve this?

You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.

For one node

declare @X xml =
'<root>
  <element xml:space = "preserve"> </element>
  <element> </element>
</root>'

select @X;

Result:

<root>
  <element xml:space="preserve"> </element>
  <element />
</root>

Entire document:

declare @X xml =
'<root xml:space = "preserve">
  <element> </element>
  <element> </element>
</root>'

select @X;

Result:

<root xml:space="preserve">
  <element> </element>
  <element> </element>
</root>

Another options for the entire document is to use convert with style 1.

  Preserve insignificant white space. This style setting sets the
  default xml:space handling to match the behavior of
  xml:space="preserve".

declare @X xml = convert(xml,
'<root>
  <element> </element>
  <element> </element>
</root>', 1)

select @X;


Please explain how this query generates a calendar table in MySQL This page has a pretty neat way of generating a robust calendar table. I've copy-pasted it below:

CREATE TABLE calendar_table (
    dt DATE NOT NULL PRIMARY KEY,
    y SMALLINT NULL,
    q tinyint NULL,
    m tinyint NULL,
    d tinyint NULL,
    dw tinyint NULL,
    monthName VARCHAR(9) NULL,
    dayName VARCHAR(9) NULL,
    w tinyint NULL,
    isWeekday BINARY(1) NULL,
    isHoliday BINARY(1) NULL,
    holidayDescr VARCHAR(32) NULL,
    isPayday BINARY(1) NULL
);

CREATE TABLE ints ( i tinyint );

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO calendar_table (dt)
SELECT DATE('2010-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 11322
ORDER BY 1;

I don't understand what is happening in the last query. Can someone explain it to me step by step?

I don't know what you want in the explanation, but the last part of the query does a cross join (Cartesian join) to generate an ordered sequence of numbers from 0 to 11322 using the digits table named ints. The query then adds these number to date '2010-01-01' as day interval, thus generating

'2010-01-01'
'2010-01-02'
'2010-01-03'
.....
.....
'2040-12-31'

etc. till you add 11322 days, that is '2040-12-31'. These dates are then inserted into the dt field of the calendar_table.

Well this is a common way of creating a date dimension table. Once the dates are inserted you can update rest of the fields. You generally want to create your organization's holiday table and a payday logic or payday table to update the holiday and payday fields. Rest of the fields can be updated with a query with date functions as below.

update calendar_table
set y = year(dt),
q = quarter(dt),
m = month(dt),
d = day(dt),
dw = dayofweek(dt),
monthName = monthname(dt),
dayName = dayname(dt),
w = week(dt),
isWeekday=
case
   when dayofweek(dt) = 1 or dayofweek(dt) = 7 then 0
   else 1
end;

Cheers.


Will query continue to run even after network times-out I have a few long-running queries that I'm executing against a remote instance of Postgres (AWS RDS).  When I execute any of these queries, I get a network timeout on my client after a couple of minutes.
Does the query continue to run even though the client connection timed-out?
These particular queries do updates and deletes, so I don't need any information back from the query, I just need to know that they completed.  I've tried with both psql (locally on my machine, not on the server; I can't run it on the server because it's on RDS), and Datagrip, but the timeout happens for both.
There's a similar question here, but it contains two contradictory answers.
Alternatively, is there a way to execute a query fire-and-forget style, where the server wont send information back at all?

If your database connection is terminated without properly closing it (from the server side: if the client just silently dies), and running query will continue until one of these two things happen:

The query produces a result row, the server tries to send it to the client and gets a network error.
The operating system starts sending keepalive packages, because the connection has been idle for a long time (by default, 2 hours). The system will lend a couple of these packages, and when there is no answer, the connection is closed. As soon as PostgreSQL checks for interrupts, the running query should terminate.

You can set the tcp_keepalives_idle parameter to adjust that timeout. This can help the server to detect broken connections more quickly.

When the client is gone, and the query is still running, system resources are used, but there is no danger that this will cause any modifications in the database: eventually the transaction (be it a single statement or a longer transaction) will encounter an error and will be rolled back.


'mysql' database in fresh MySQL installation I installed MySQL database and according to show databases command I have a database named 'mysql'. Is this database used for holding some internal values for MySQL? In addition, is it possible to view the content of rows of tables in 'mysql' database? Usual SELECT * FROM tablename does not seem to work:

mysql> describe servers;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM servers;
Empty set (0.00 sec)

mysql>

The mysql.servers table is used only in conjunction with any table that uses the FEDERATED Storage Engine. It's in the MySQL Documentation:

  FEDERATED tables may be replicated to other slaves, but you must ensure that the slave servers are able to use the user/password combination that is defined in the CONNECTION string (or the row in the mysql.servers table) to connect to the remote server.

Therefore, a freshly installed MySQL instance will have mysql.servers empty. If you create a FEDERATED table, then the username/password for a remote table is populated in mysql.servers.

What you are looking for is the information_schema database. For example,

INFORMATION_SCHEMA.SCHEMATA has all the databases in the MySQL Instance
INFORMATION_SCHEMA.TABLES has all the tables in the MySQL Instance
Please Read the Following

MySQL Documentation on INFORMATION_SCHEMA
My 4-Year-Old Post How is INFORMATION_SCHEMA implemented in MySQL?


How does the database decide which Index to use DDL

create table t
(
    id int,
    id1 int
)

create index Example_Index
  On t(id,id1)

create index Example1_Index
  On t(id1,id)

DML

insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)

Sql Query - select * from t Where id = 107  -- uses Example_Index

Sql Query - select * from t Where id1 = 107 -- uses Example1_Index

Sql Query - select * from t                 -- uses Example1_Index

Confusion - How does the system decides to use Example1_Index

Below are the details...

First two queries are self-explanatory - they're performing a seek on the leading column in the chosen index.

Third one is a coin-flip since, without a clustered index, both indexes have the same cost.

You might observe that the most recently-used index (from query 2) will be used for query 3 - but you shouldn't rely on it.


How to avoid constantly querying a server I want to compare 2 table each on their own server.  I want to avoid constantly querying between the 2 server.  Would something like a temp table work well in this case?  Do I need to use linked server and extract the table into a temp table so I only query the server once?  Thanks.

Note you should include which SQL Server version you're using, as this will impact what answers are applicable to your use case.
If you're trying to alleviate pulling data across remote servers, then you should look into a data synchronization feature like Replication, AlwaysOn Availability Groups, or SISS
These can be used to synchronize the data from a remote server to a local server, so your querying can all be contained to one server.


Save postgres database from broken ubuntu 10.04 server Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve).

I can still access the filesystem. Is there a chance to transfer the database by only moving some directories with content to an equal machine?

This is a 32 bit ubuntu 10.04 with postgres 8.4

Edit: On ubuntu 10,04 the postgresql data directory is /var/lib/postgresql/8.4/main and not /usr/local/pgsql/data

I think backing up the "data directory" is what you want.

Moving the whole directory to a new server should be what you might look into. This will move everything within the cluster. From there, you can drop, move,... your single databases as you need to.


Generate metadata file from PostgreSQL server Is there a way that I can make a metadata-sheet that lists all the available tables from a postgresql-database, so users can see documentation of the tables?

I know that there are descriptions, column names, number of rows and several more information that could do as metadata for the specific database. But when i have something like 36 tables I'd like to make a documentation of, then I am looking for at way to just generate a table of the metadata of the tables.

If you want something quick and dirty, I would just use psql to dump out all the tables.

for t in $(psql -Ac '\dt' | awk -F'|' '$3 == "table" {print $2}');
do
    psql -c "\\d $t"
done > metadata.tables

Job done.


SQL Server: How can I read server-side traces with limited permission? I am trying to set up a trace on a remote server.  I am able to generate the trace, set filters, and even stop and delete the trace through sql server without an issue.  However, the file created with sp_trace_create seems to be owned by the SQL Service and I do not have administrative permission to grant myself read-access.

Is there something similar to Oracle's trace_files_public for SQL Server 2008 R2?

  "There is an undocumented parameter _trace_files_public that if set to
  true changes the file permissions in the user_dump_dest directory when
  trace files are created to allow everyone to read them."
  (http://www.petefinnigan.com/ramblings/how_to_set_trace.htm)

Is there a way I can view the contents through SMSS (since I seem to have adequate permission there, if not to the file itself?)
Something else that would be even better I haven't considered?

Sorry if my terminologies are rough--the whole dba thing is new to me.

Thanks in advance!  My sys-admin will appreciate not being bugged to set permissions (again.)

To view the trace file contents in SMSS, I am using the following function: FN_TRACE_GETTABLE. To run FN_TRACE_GETTABLE, the users don't need any permissions on the server's file system. Only the account that is running the service must have the permissions to access the trace file, but it already has them, because it has created the trace file in the first place.

I have an automated job that uploads a trace file into a table every weekend, and neither me nor the account running the job have any permissions on the server's file system. This is running 2008 R2.

When I was developing that job, I had run FN_TRACE_GETTABLE from SSMS quite a few times.


Why is the result of a totally deterministic and all-encompassing "case" in select query in view nullable? I have a table, with Id as the primary key.

create table Anything
(
    Id bigint not null primary key identity(1, 1)
)

When I look at this table in Object Explorer, I see this image of course:

As you can see, column Id is not null.

Then I create a dummy view on this dummy table:

create view IdIsTwoView
as

select
    Id,
    (
        case
        when Id = 2
        then cast(1 as bit)
        else cast(0 as bit)
        end
    ) as IdIsTwo
from Anything

But this time, in Object Explorer I see this result:

As you can see, in spite of this fact that my case clause is all-encompassing and covers 100% of all records, and has an answer for all records, it's nullable.

Why does SQL Server have this weird behavior? And how do I force it to be not null?

P.S we have an infrastructure to generate code dynamically, and this behavior causes us trouble and we have to manually go and change all bool? types in C# to bool.


MySQL event scheduler waiting on empty queue since server restarted 12 days ago I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted.
mysql> SHOW FULL PROCESSLIST;

+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
| Id      | User            | Host      | db   | Command | Time    | State                  | Info                  |
+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
|       5 | event_scheduler | localhost | NULL | Daemon  | 1098372 | Waiting on empty queue | NULL                  |
| 1774483 | root            | localhost | NULL | Query   |       0 | starting               | SHOW FULL PROCESSLIST |
+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
2 rows in set (0.00 sec)

There are no events, and I haven't attempted to created any.
mysql> SELECT * FROM information_schema.EVENTS;

Empty set (0.00 sec)

This is actively using up to 8% of my server's CPU.
Is there a way of determining what this is, or why it was started? Will this try to run every time I restart MySQL? If so, what is it 'waiting' for and do I need to tweak my configuration at all to prevent this?
MySQL 8.0.21

The event_Scheduler is, as it's name suggests, a way to Schedule Events (in this case queries) to run in MySQL at a given time. It is simply waiting for an event to trigger it and tell it to do something. As you can see you have no events set up, so it will be waiting for a long time.
It is enabled by default, but can be disabled by running:
SET @@global.event_scheduler = 0;
You also want to add:
event_scheduler = OFF
to your my.cnf file to prevent it starting after a reboot.


Can a trigger with an "insert into" statement cause Intext Exclusive locks for small insert operations? We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as new rows with a different value in a certain column.  The insert operations in the Nodes table occurs one at a time (so no single large batch insert operations, Entity Framework) and the select/insert operation into the NodeClosures table is causing an intent exclusive lock.  I'm uncertain if this is due to lock escalation (not sure how as neither insert operations are inserting more than 6 rows).  Some additional information, Read Committed Snapshot Isolation is being used as well as Delayed Durability Transactions.
I have included the full trigger for reference (MSSQL 2017):

CREATE TRIGGER [tr_dbNode_insert]
ON [dbo].[DbNodes]
After INSERT
AS
BEGIN
    -- Insert self closure
    insert into DbNodeClosures (ParentID, ChildID, Depth, AccountID)
    select Id, Id, 0, AccountID
    from inserted;
    -- Insert parent's closures as it's own + 1
    insert into DbNodeClosures (ParentID, ChildID, Depth, AccountID)
    select closures.ParentID, inserted.Id, closures.Depth + 1, inserted.AccountID
    from inserted join DbNodeClosures as closures on inserted.ParentID = closures.ChildID;

END

The short answer to your question is - Yes, any insert, even a single row, can cause an Intent-Exclusive lock, but on the higher levels in the lock hierarchy than the actual Exclusive lock that was taken.

The Intent-Exclusive lock indicates that a request has taken an Exclusive lock at a lower level in the Lock Hierarchy (Database (High) -> Table -> Page -> Row (Low)). For example, if you're seeing the IX lock at the table level, it indicates a request has an X lock at the page or row level. If you see the IX lock at the page level, then there is an X lock at the row level. Note that in lock escalation, Row doesn't escalate to Page, instead both Row and Page locks escalate to Table locks.

The Intent Exclusive lock isn't indicating lock escalation per se, it is there to control locks on higher-level resources to ensure the isolation level of all transactions is respected. If your trigger is updating 6 rows then it will most likely only have row or page level Exclusive locks, and the table-level will show an Intent Exclusive lock. This affects other sessions trying to take locks on higher-level resources by indicating lower-level resources are already locked.

Check out this article for a good explanation of the different locks and their compatibility with one another.


Where is MySQL template directory for pristine Data? I'm using MySQL 8.0 in Windows.
I'm trying to set up multiple servers.  And I need to copy the Data directory for it. The MySQL documentation says

Windows MSI package installers create and set up the data directory
that the installed server will use, but also create a pristine
“template” data directory named data under the installation directory.
After an installation has been performed using an MSI package, the
template data directory can be copied to set up additional MySQL
instances.

It says there is a pristine data directory that is not touched. But my Data directories are already modified because I used it quite a long time. I need to copy the Data directory to make new server.
Where do I find the pristine Data directory?

For anyone met the same problem as me, I couldn't find the pristine Data directory. I looked everywhere but I couldn't. So I reinstalled the new copy on my second computer. And copy the Data folder from it. Now, my multiple servers work without any problem in one machine.


How to preserve the original order of elements in an unnested array? Given the string:

  'I think that PostgreSQL is nifty'

I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and would like to join an unnested array of that string on this dictionary.

So far I have:

select word, meaning, partofspeech
from unnest(string_to_array('I think that PostgreSQL is nifty',' ')) as word
from table t
join dictionary d
on t.word = d.wordname;

This accomplishes the fundamentals of what I was hoping to do, but it does not preserve the original word order.

Related question:
PostgreSQL unnest() with element number

WITH ORDINALITY in Postgres 9.4 or later
The new feature simplifies this class of problems. The above query can now simply be:
SELECT *
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn);
Or, applied to a table:
SELECT *
FROM   tbl t, regexp_split_to_table(t.my_column, ' ') WITH ORDINALITY x(word, rn);
Details:

PostgreSQL unnest() with element number

About the implicit LATERAL join:

What is the difference between LATERAL and a subquery in PostgreSQL?

Postgres 9.3 or older - and more general explanation
For a single string
You can apply the window function row_number() to remember the order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the original position in the string.
You could simply omit ORDER BY to get the position "as is":
SELECT *, row_number() OVER () AS rn
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') AS x(word);
Performance of regexp_split_to_table() degrades with long strings.  unnest(string_to_array(...)) scales better:
SELECT *, row_number() OVER () AS rn
FROM   unnest(string_to_array('I think Postgres is nifty', ' ')) AS x(word);
However, while this normally works and I have never seen it break in simple queries, Postgres asserts nothing as to the order of rows without an explicit ORDER BY.
To guarantee ordinal numbers of elements in the original string, use generate_subscript() (improved with comment by @deszo):
SELECT arr[rn] AS word, rn
FROM   (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM   string_to_array('I think Postgres is nifty', ' ') AS x(arr)
   ) y;
For a table of strings
Add PARTITION BY id to the OVER clause ...
Demo table:
CREATE TEMP TABLE strings(string text);
INSERT INTO strings VALUES
  ('I think Postgres is nifty')
 ,('And it keeps getting better');

I use ctid as ad-hoc substitute for a primary key. If you have one (or any unique column) use that instead.
SELECT *, row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid, unnest(string_to_array(string, ' ')) AS word
   FROM   strings
   ) x;

This works without any distinct ID:
SELECT arr[rn] AS word, rn
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM  (
      SELECT string_to_array(string, ' ') AS arr
      FROM   strings
      ) x
   ) y;

SQL Fiddle.
Answer to question
SELECT z.arr, z.rn, z.word, d.meaning   -- , partofspeech -- ?
FROM  (
   SELECT *, arr[rn] AS word
   FROM  (
      SELECT *, generate_subscripts(arr, 1) AS rn
      FROM  (
         SELECT string_to_array(string, ' ') AS arr
         FROM   strings
         ) x
      ) y
   ) z
JOIN   dictionary d ON d.wordname = z.word
ORDER  BY z.arr, z.rn;


Preventing SQL Injection I know that this is very dangerous:

EXEC sp_executesql 'SELECT * FROM ' + @Table

But, is this safe from SQL injection?

IF EXISTS(SELECT * FROM information_schema.tables WHERE TABLE_NAME = @Table) BEGIN
    EXEC sp_executesql 'SELECT * FROM ' + @Table
END

There is still a potential for injection here. Let's assume that:

someone has the ability to create tables in this database, but not drop them
that someone has the ability to call this procedure
the procedure runs as an elevated user

That person could call this procedure to get results from foo, but in the meantime create a table called [foo;drop table bar], then pass foo;drop table bar in as the parameter value. It would pass your check, then you would blindly execute it. They get the results from foo but they also drop table bar.

Now, that is a pretty contrived scenario, and you probably aren't worried about internal people dropping tables, but they could also use this to exploit data they don't have access to, for example they could create a table called [foo;SELECT name,salary FROM dbo.Employees;] and end up with two result sets, one with data you probably shouldn't be letting them see.

There is also a potential for getting data from the wrong table, in the event that there are tables with the same names in different schemas. Always, always, always use the schema prefix when creating or referencing objects:

Bad habits to kick : Avoiding the schema prefix

Finally, don't use INFORMATION_SCHEMA. The catalog views and metadata functions are much more complete, current and reliable:

The case against INFORMATION_SCHEMA views

I would probably do it this way (and I'm going to assume for simplicity that all of your tables are in dbo):

DECLARE @table SYSNAME; -- procedure parameter, right?

DECLARE @sql NVARCHAR(MAX);
IF OBJECT_ID(N'dbo.' + QUOTENAME(@table), N'U') IS NOT NULL
BEGIN
  SET @sql = N'SELECT * FROM dbo.' + QUOTENAME(@table) + ';';
  EXEC sp_executesql @sql;
END

This way, even if they pass foo;drop table bar or foo;select name,salary from dbo.employees as the table name, that whole string gets surrounded in square brackets, thus the worst thing that can happen is they'll get the results from the table they created, rather than any other table or worse.

And I would ensure that the procedure runs as that user, rather than escalate using EXECUTE AS (a common bypass for proper permissions configuration). Also see:

Bad habits to kick : Using SELECT *


Does using PK and FK effect performance in negative way? Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up more memory. Any insight?

You should tag which database system and version you're referring to, as this could affect the answer that is appropriate. But assuming you mean one of the main modern relational database systems, there's a couple of different objects you're asking about, so here's an answer for each of them:
Primary Keys:
They do not affect performance in a negative way at all, and in fact can slightly help performance in certain database systems because they guarantee uniqueness on a set of fields in the table. It is bad relational design in most cases to not define a primary key on a table. Primary keys are not the same thing as clustered indexes, though some database systems will create the clustered index on the primary key fields by default, out of assumption the primary key fields will be used in predicates (JOIN, WHERE and HAVING clauses).
Foreign Keys:
Similar to primary keys these also have no measurable negative performance impact, and can also potentially help performance by guiding the database system to generate a more efficient execution plan in specific cases. (I find this to be a rare occurrence though and most times there's no performance difference one way or the other.)
Clustered Indexes:
The clustered index on a table can definitely improve performance when the fields of that index are commonly queried on in a predicate, and in most cases it's recommended to have one on a table. The clustered index is the logical ordering of the data in the table itself and therefore doesn't take up any additional space. Without a clustered index the table is logically stored in a heap (or similar data structure, depending on database system) which can be inefficient to read data from. A clustered index on a table can result in INSERT operations taking slightly longer, but the difference of generally milliseconds for INSERT operations is usually worth the tradeoff for the major performance improvements of SELECT, UPDATE, and DELETE queries.
The edge cases where there might be a net loss in performance from using a clustered index is when you use a staging table to temporarily store the data before it's moved elsewhere, and you don't plan to do any querying against that table other than INSERT operations or selecting the entire table.


PgBouncer logging details for `query_wait_timeout` error Regarding this type of error in PgBouncer log:

2020-03-10 03:46:01.730 EDT [25305] LOG C-0xa5cf00: testdb/testdb_user@127.0.0.1:49214 closing because: query_wait_timeout (age=120s)
2020-03-10 03:46:01.730 EDT [25305] WARNING C-0xa5cf00: testdb/testdb_user@127.0.0.1:49214 pooler error: query_wait_timeout

Is there any possibility to see (or to log) the query that triggered the error? What are the most frequent causes for this error? a stopped server, a busy server?

UPDATE: the log file from PostgreSQL does not show any error at 2020-03-10 03:46:01

The log message you shared is not due to some error with a query.  Rather, it is due to a pgbouncer client session that is waiting for a server connection, but that wait took more than 2 minutes.  In other words, all real-connections in a pool have been used, and some session was waiting around for 2 minutes to get an opportunity to run a query on the database.  There could be a number of factors causing this situation:

Session-level pooling -- you may have several pgbouncer sessions that get real connections to the database, but are idle and never releasing a connection back to the pool.  Then, the newest session just waits around and never gets a chance to run its query (and query_wait_timeout happens, and the new pgbouncer session is booted)
Transaction-level pooling, pool size too small -- you may just have a lot of connections from applications and they spend a lot of time running a transaction, such that any new sessions are stuck waiting around for the transactions to finish, but 2 minutes was not enough time.
query_wait_timeout too small -- you might just have lots of long-running queries (check pg_stat_activity for any old queries that are still running, or for idle transactions)

There may be a few other possible reasons for what you're seeing, but these are just a few that came to mind.