Add column with a sum total to crosstab() query in PostgreSQL 9.0 Following on from my previous question:

Creating crosstab() pivot table in PostgreSQL 9.0

I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create a view or table of the base geometry-less table.

However, this still isn't much use as I need to link it to the gazetteers_and_addresses.unit_postcode table in order to assign geometries for further analysis.

I will attach the table structure for both tables and the original code that worked to create my crosstab.

CREATE OR REPLACE VIEW adult_social_care.vw_ageband AS (
    SELECT * FROM crosstab(
        'SELECT postcode_nospace_, ageband, count(ageband) as total_count
         FROM adult_social_care.activities_in_localities_asc
         GROUP BY postcode_nospace_, ageband
         ORDER BY postcode_nospace_'

         ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
    AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric));

Table defintions:

activities_in_localities_asc:

CREATE TABLE adult_social_care.activities_in_localities_asc (
  ogc_fid integer NOT NULL,
  sort numeric(5,0),
  ageband character(12),
  postcode_nospace_ character(8),
  wkb_geometry geometry,
  CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)
);

unit_postcode:

CREATE TABLE gazetteers_and_addresses.unit_postcode (
  oogc_fid serial NOT NULL,
  pc_area character(10),
  postcode_nospaces text,
  wkb_geometry geometry
);

If possible too, assign another field at the end which states a sum of all the fields to give a total_count.

If this can be done then I can create dynamic views on different factors using one geometry-less table and unit_postcode.

Proper crosstab query
First of all, the presented query would not work while you still have char(n) in your table definition - like we discussed under your previous question. You would need to convert to text or varchar first.
This works with your current table definition:
SELECT * FROM crosstab(
   'SELECT postcode_nospace_::text, ageband::text, count(ageband) AS ct
    FROM   adult_social_care.activities_in_localities_asc
    GROUP  BY 1, 2
    ORDER  BY 1'

   ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
           , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$
   )
AS t("postcode" text
   , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
   , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint);
I am also using bigint instead of numeric to save another unnecessary type conversion (count() returns bigint).
Detailed explanation:

PostgreSQL Crosstab Query

Better table definition
But better ALTER the data type in your table to text like @dezso suggested. Or, if you want to keep the length limit, add a CHECK constraint or use varchar(n). Never use char(n).
ALTER TABLE activities_in_localities_asc
   ALTER COLUMN ageband TYPE varchar(12)
 , ALTER COLUMN postcode_nospace_ TYPE varchar(8);

 ALTER TABLE gazetteers_and_addresses.unit_postcode
   ALTER COLUMN pc_area TYPE varchar(10);

Then you don't need to cast original column values any more:
SELECT * FROM crosstab(
   'SELECT postcode_nospace_, ageband, count(ageband)
    ...

Ideally, though, ageband should be an enum or (my preference) an ID referencing a lookup table, not plain, error-prone text ... key word: normalization.
Add total count
Building on your table, but with text or varchar() columns.
Wrap your original query into a CTE and call the the result twice in a UNION query - the 2nd call adds totals per postcode:
SELECT * FROM crosstab(
  $$WITH cte AS (
      SELECT postcode_nospace_, ageband, count(ageband) AS ct
      FROM   adult_social_care.activities_in_localities_asc
      GROUP  BY 1, 2
      )
   TABLE  cte  -- original results
   UNION ALL   -- add total per postcode
   SELECT postcode_nospace_, 'total' AS ageband, sum(ct) AS ct
   FROM   cte
   GROUP  BY 1
   ORDER  BY 1$$  -- dollar-quotes to include single quotes easily

   ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64')
           , ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER'), ('total')$$
   )
AS t("postcode" text
   , "18-24" bigint, "25-34" bigint,"35-44" bigint, "45-54" bigint, "55-64" bigint
   , "65-74" bigint, "75-84" bigint, "85-94" bigint, "95 AND OVER" bigint, "total" bigint);
About the TABLE command:

Is there a shortcut for SELECT * FROM in psql?

Related CTE examples:

Combine results of two queries, where the second relies on the first
Get n grouped categories and sum others into one


Interval values for aggregate function AVG greater than 24 hours I have a simple query which runs the AVG aggregate function on a date time interval field generated by subtracting one datetime field from another.

The query looks like :

SELECT AVG(date_field_1 - date_field_2)
FROM table
WHERE date_field_1 > date_field_2
AND date_field_1 IS NOT NULL
AND date_field_2 IS NOT NULL
....

However the query is returning values such as 2 days 24:50:26.092199 in the default postgres interval style . The absolute value I get when I extract EPOCH and convert  to  seconds (EXTRACT(EPOCH FROM (AVG(date_field_1 - date_field_2))) is something like 262226.092199. I also get results like 26 hours not just 24 hours.

Should the output not be 3 days 00:50:26.092199? .

As @horse_with_no_name already comment, you can use the justify_* funcions, take a look:

WITH config AS (
SELECT '3 days 0 hours 50 minutes 26.092199 seconds'::interval as reference
), tests AS (
    SELECT
        extract(epoch from reference) as epoch
    FROM config
)
SELECT
    config.reference,
    tests.epoch,
    (tests.epoch::text || ' seconds ')::interval,
    justify_days(config.reference) as justify_days,
    justify_hours(config.reference) as justify_hours,
    justify_interval(config.reference) as justify_interval
FROM config, tests;

Take a look at the output:

postgres=# \x
Expanded display is on.
postgres=# WITH config AS (
postgres(# SELECT '3 days 0 hours 50 minutes 26.092199 seconds'::interval as reference
postgres(# ), tests AS (
postgres(#     SELECT
postgres(#         extract(epoch from reference) as epoch
postgres(#     FROM config
postgres(# )
postgres-# SELECT
postgres-#     config.reference,
postgres-#     tests.epoch,
postgres-#     (tests.epoch::text || ' seconds ')::interval,
postgres-#     justify_days(config.reference) as justify_days,
postgres-#     justify_hours(config.reference) as justify_hours,
postgres-#     justify_interval(config.reference) as justify_interval
postgres-# FROM config, tests;
-[ RECORD 1 ]----+-----------------------
reference        | 3 days 00:50:26.092199
epoch            | 262226.092199
interval         | 72:50:26.092199
justify_days     | 3 days 00:50:26.092199
justify_hours    | 3 days 00:50:26.092199
justify_interval | 3 days 00:50:26.092199

Please, take a look at the documentation[1] for more details.

I hope it helps.

References:

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TABLE


How do I display rows as CSV? The default output format for a query is something like this:

ID | field1
-----------
1  | val1
2  | val2

Is it possible in SQL to change it to a CSV format like this?

ID , field1
1  , val1
2  , val2

Currently, I am converting fields to string values and then concatenating them using the || operator, e.g.

SELECT ID::text || ', ' || field1 ...
FROM ...

But this is really cumbersome to write, and not very readable either.

Is there a more succinct way of doing this without converting to strings?

This isn't a duplicate of Export remote Postgres table to CSV file on local machine because as shown in my example, I am looking to output in CSV, not store the output as a CSV file.

You can copy a query results directly into a CSV file, e.g.

copy (
    select id, field1
    from (
        values (1, 'val1'), (2, 'val2')
        ) as v(id, field1)
) to '/data/test.csv' (format csv, header);

The file /data/test.csv:

id,field1
1,val1
2,val2

Use the function concat_ws() to get an output in expected format. Base types will be automatically converted to texts:

select concat_ws(', ', id, field1, date1, dec1)
from (
    values
        (1::int, 'val1', '2017-01-01'::date, 1.1::dec),
        (2::int, 'val2', '2017-01-02'::date, 1.2::dec)
    ) as v(id, field1, date1, dec1)

        concat_ws
--------------------------
 1, val1, 2017-01-01, 1.1
 2, val2, 2017-01-02, 1.2
(2 rows)


Remote database connection problem? I have two systems with Windows XP. I have installed SQL Server 2005 on a system and tried to use SQL Server Management Studio Express on both to use the database.

I am able to use the database on the system on which I have installed it, but on the other system I get the error:

  remote database access problem

I am using Windows authentication mode on SQL Server 2005.

Is it possible to connect the database remotely or not? If yes, then how I can do it? Any other ideas to resolve this issue?

Yes, you can use SQL Server Management Studio, even if it's an Express version to connect to any SQL Server,you have TCP/IP connectivity to. Just launch it, enter the DNS host name or IP address in the 'Server Name' box and hit Connect.

Two things may prevent this from working:

Your SQL Server isn't set up for TCP/IP connectivity. This is the default setting from version 2005 onwards, and can be changed using the SQL Server Configuration Manager.

There is a firewall between you and your SQL Server blocking TCP/IP traffic. This is an entirely sensible construction: you do NOT want your database server to be available from the general Internet, as this is a huge security risk. In fact, if your hosting company allows this kind of access by default, I'd be looking for a different provider...

Anyway, what seems to be needed in your scenario is: a) the hosting company enabling TCP/IP on your SQL Server instance, b) them providing you with secure access to the IP address that instance is running on. This will involve some kind of VPN or SSH port-forwarding soluition, or at least an IP filter, where only 'trusted' IP addresses can access the SQL Server (which is not a great solution, but can be used if nothing else can be implemented). You'll have to discuss these requirements with your hosting company.


Nest SUM() Function Inside Case Statement? I am trying to run this syntax, but I get an error of

  Msg 4145, Level 15, State 1, Line 9
  An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'.

And this is my syntax, just trying to conditionally execute a SUM() based off the criteria.

SELECT
    CASE WHEN
         SUM(case when raaa.EN = 'Yes' AND raaa.HR >= raaa.WT then raaa.WT end)
         SUM(case when raaa.EN = 'Yes' AND raaa.HR <= raaa.WT then raaa.HR end)
         SUM(case when raaa.EN = 'No' AND raaa.HR <= raaa.WT then raaa.HR end)
         SUM(case when raaa.EN = 'No' AND raaa.HR >= raaa.WT then raaa.WT end)
    END AS HR
FROM
        ranaboveaboardabout raaa;

EDIT
I know that this will work, just can't seem to get syntax correct for the above

Select
CASE WHEN
        SUM(raaa.field1) / SUM(CASE WHEN raaa.field2 < raaa.field1 THEN raaa.field2 ELSE raaa.field1 END) >= 16.23 THEN 20
        ELSE SUM(raaa.field1) / SUM(CASE WHEN raaa.field2 < raaa.field1 THEN raaa.field2 ELSE raaa.field1 END)
END AS nestedSUM
FROM
    ranaboveaboardabout raaa;

You need to remove the outer case statement. It should be:

Select sum( case when ..... then .... end) from foo

You have

Select case when sum(case when.... then .... end) end from foo

The when clause expects something that will resolve to either true or false. That's what your error message is trying to tell you.

I'm still not 100% sure what your end goal is but you either need to remove the wrapping case statement like I said above or pull the case condition out of the sum.

Select
   Case
       When <condition > then sum(<column>)
        When <condition > then sum(<column >)
         Else sum(<column >)
         End
From foo


Recursive CTE with partition I have a table like this in MS SQL SERVER 2014:
ID|Race|Lap
1 |21  |11
2 |21  |NULL
3 |21  |NULL
4 |21  |NULL
5 |29  |65
6 |29  |NULL
7 |29  |NULL
8 |29  |NULL

I am trying to fill up the Lap column by adding 1 to it based on the first value. The partition is based on Race column. Something like this would be the end result:
ID|Race|Lap
1 |21  |11
2 |21  |12
3 |21  |13
4 |21  |14
5 |29  |65
6 |29  |66
7 |29  |67
8 |29  |68

There might be other ways of doing this but I would rather stick with recursive CTE. Is there any way to do this?

This would produce the expected result:
create table #demo (id int, race int, lap int)
insert into #demo values (1,21,11),(2,21,null),(3,21,null),(4,21,null),(5,29,65),(6,29,null),(7,29,null),(8,29,null);

with CTE as
(select race, ROW_NUMBER() over (partition by race  order by race) "extra_lap" from #demo where lap is null),
CTE2 as
(select race, lap "lap" from #demo where lap is not null)
select race, lap from CTE2
union
select CTE.race, CTE2.lap + CTE.extra_lap "lap" from CTE join CTE2 on CTE.race=CTE2.race

drop table #demo;


A question about foreign keys and many to many relationships My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school can have many students, and a student can attend many schools. I'm hung up on the problem of whether to include the school as a foreign key in the tables that are directly beneath the Student table in the information hierarchy (Behavior, Attendance, Grade).

In short, the many-to-many relationship between School and Student allows for the possibility that a student referenced in the Attendance table would have multiple schools listed in the linked Student table. This could result in unwanted ambiguity when going over attendance records. (i.e. Did they miss class in school A or school B?) My solution is obvious: include a School foreign key for any tables beneath Student to make their relationship to a given school explicit.

The problem: this seems inelegant. Aesthete that I am, I would like the information to flow through tables, instead of jumping tables.  A convenient solution I've encountered would be to rely on a polymorphic association, wherein the primary key passed to the tables beneath Student (Behavior, Attendance, Grade) provides unique values for both the student and school. Unfortunately, it appears that SQL won't tolerate that approach.

Is there a solution that allows me to enjoy the apparently sensible data flow offered by polymorphic association (illustrated below) without breaking the rules of SQL, or am I trying to have my cake and eat it to?

Given that a school can have many students, and a student can attend many schools

.. you should have a many-to-many relationship between school and student - implemented as separate table. (school_id is misplaced in the table student.) I'll simply name it school_student:

CREATE TABLE school_student (
   school_student_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
 , school_id  int NOT NULL REFERENCES school
 , student_id int NOT NULL REFERENCES student
 , ...
);

And then use school_student_id in tables behavior and attendance.
Alternatively, you can use a multicolumn PK on (school_id, student_id) and carry over (school_id, student_id) as FK - effectively implementing what you had in mind - with standard SQL. Like:

CREATE TABLE school_student (
 , school_id  int NOT NULL REFERENCES school
 , student_id int NOT NULL REFERENCES student
 , ...
 , PRIMARY KEY (school_id, student_id)
);

You don't need that for grade, since that references a course bound to a school already. But you might implement something to enforce integrity so that students can only attend courses at schools they are enrolled at. If you use (school_id, student_id) as FK, that can be enforced automatically - using  a single copy of school_id for both FKs in table grade.

Related:

Enforcing constraints "two tables away"


Selecting data from a mapping table using a foreign key I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL.
After searching for how to do it best i came across Mapping (pretty much making a 3d Table containing both USR_ID and Element_ID, both of course being forign keys)
CREATE TABLE [dbo].[Users] (
[USR_ID]        INT          IDENTITY (1, 1) NOT NULL,
[USR_UserName]  VARCHAR (50) NOT NULL,
[USR_Password]  VARCHAR (50) NOT NULL,
[USR_FirstName] VARCHAR (50) NOT NULL,
[USR_LastName]  VARCHAR (50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([USR_ID] ASC));

CREATE TABLE [dbo].[GameList] (
[GL_ID]          INT             IDENTITY (1, 1) NOT NULL,
[GL_Title]       NVARCHAR (MAX)  NOT NULL,
[GL_Genre]       NVARCHAR (MAX)  NOT NULL,
[GL_Plattform]   NVARCHAR (MAX)  NOT NULL,
[GL_ReleaseDate] DATETIME2 (7)   NOT NULL,
[GL_Price]       DECIMAL (18, 2) NOT NULL,
CONSTRAINT [PK_GameList] PRIMARY KEY CLUSTERED ([GL_ID] ASC));

CREATE TABLE [dbo].[UserGame] (
[UG_ID]  INT IDENTITY (1, 1) NOT NULL,
[USR_ID] INT NOT NULL,
[GL_ID]  INT NOT NULL,
CONSTRAINT [PK_UserGame] PRIMARY KEY CLUSTERED ([UG_ID] ASC),
CONSTRAINT [FK_USR_ID] FOREIGN KEY ([USR_ID]) REFERENCES [dbo].[Users] ([USR_ID]),
CONSTRAINT [FK_GL_ID] FOREIGN KEY ([GL_ID]) REFERENCES [dbo].[GameList] ([GL_ID]));

Those are how i created the 3 Tables and my Qestion is how can i select all the Games from dbo.GameList that has the same USR_ID
If it was like using USR_ID a normal forign key i wouold've probably used something like
SELECT GameList.Title, GameList.Genre, GameList.Plattform, GameList.ReleaseDate, GameList.Price
FROM GameList
INNER JOIN Users ON GameList.USR_ID=Users.USR_ID;

But since it's my first time using mapping i'm clueless.
Thanks in Advance !!

It is the same
SELECT gl.Title, gl.Genre, gl.Plattform, gl.ReleaseDate, gl.Price
FROM GameList gl
INNER JOIN UserGame ug ON gl.GL_ID = ug.GL_ID
WHERE ug.USR_ID = 10;

As long you have the User_id you don't need more, but if you also want information from the users table
SELECT gl.Title, gl.Genre, gl.Plattform, gl.ReleaseDate, gl.Price
FROM GameList gl
INNER JOIN UserGame ug ON gl.GL_ID = ug.GL_ID
INNER JOIN Users u ON ug.USR_ID=u.USR_ID
WHERE ug.USR_ID = 10;


list of PostgreSQL trusted languages? I am trying to find a list of PostgreSQL trusted procedural languages (PL) as I often need to use a PL in a non-superuser environment.

But I couldn't find a definitive answer. For example, the Postgres documentation here suggest that there is a both a trusted and an untrusted PL for perl, a language that I am not very familiar with. Here, it says the TRUSTED keyword is something you can specify when creating a PL language, which may be ignored under circumstances.

But I also have the (possibly wrong) impression that certain PL languages are trusted only (e.g. JavaScript PL/V8) or untruested only (Python). Other posts seem to suggest that one can tweak pg to make even python a trusted language with something like:

UPDATE pg_language SET lanpltrusted = true WHERE lanname = 'plpythonu';

My question is more practical than theoretical. Basically:

Is it generally true that any PL can be both set up to be trusted and untrusted?

If not, what are the specific languages that can be set up to be trusted or are trusted.

(By "trusted", I mean normal users should have effectively the same rights when creating/executing/modifying functions in the PL language. Also, I am using Postgres 9.x)

Is it generally true that any PL can be both set up to be trusted and untrusted?

No. Any PL can be marked as trusted or untrusted, but if you park a PL that doesn't support running trusted as trusted, it'll either notice that and ERROR or it'll silently run without sandboxing as if it were untrusted.

Don't modify catalogs directly.

  If not, what are the specific languages that can be set up to be trusted or are trusted.

Just these,

PL/PgSQL runs as trusted.
PL/Perl can run as trusted or untrusted.
PL/TCL can run as trusted or untrusted.

That's it for the built-in PLs. Notably, PL/Python cannot run as trusted due to fundamental design limitations with the CPython interpreter that make creation of a restricted execution environment impossible. It's possible to create a "trusted-ish" Python runtime where an attacker is protected from simple mistakes but can break out of the sandbox with effort, but PL/Python does not support this, nor is any patch to enable it likely to be accepted.

There are also a bunch of out-of-tree procedural languages, some of which support trusted mode. Notably, PL/V8 (JavaScript), PL/Lua and PL/Java support trusted mode. There are probably others.

(On a side note, I really would love to see adoption of in-core a trusted-enabled PL/Javascript as a default in-core PL, or even better, PL/C# for .NET via Mono or .NET Core).


How can I find the longest record (in characters) for each column in a table in PostgreSQL I couldn't find the answer to this on here so sorry if repeated. I'm looking to query a table to find the longest record in characters for each column in a specific table.

I have a table which is built of fields which are type: varchar and length:254

In certain cases, I know for a fact that the longest record is a single character so the problem is when exporting to shapefiles the .dbf file is over 1gb when not needed.

If someone can also help with a script to reduce the lengths of each column as well that would be great!

The below worked for what I needed:

SELECT
        MAX(char_length(column1)) as column1,
        MAX(char_length(column2)) as column2
FROM
        schema.table


Is there a way to suppress DBCC TRACEON/OFF messages in the Error Log? We have quite a few dataloading processes that run throughout the day all day long.  These processes are setting the 610 flag on for their connection then turning it off when they are done.  Because these messages are put into the ERROR LOG, our ERROR LOG is growing quite a bit.  We've tried using the WITH NO_INFOMSGS parameter of DBCC but it still puts the entry in the ERROR LOG (even though it doesn't put it in the query output).

Any ideas?

The messages look like this:

  Date      10/23/2014 8:32:46 AM Log       SQL Server (Current - 10/23/2014
  8:31:00 AM)

  Source        spid57

  Message DBCC TRACEON 610, server process ID (SPID) 57. This is an
  informational message only; no user action is required.

...

  Date      10/23/2014 8:32:54 AM Log       SQL Server (Current - 10/23/2014
  8:31:00 AM)

  Source        spid57

  Message DBCC TRACEOFF 610, server process ID (SPID) 57. This is an
  informational message only; no user action is required.

trace flag 2505 can be used. Wrote a blog post with experiment http://sqlserver-help.com/2014/12/09/help-how-to-suppress-dbcc-traceonoff-messages-in-the-error-log


JOIN in 4 tables gets phpmyadmin unresponsive I can´t manage to create a valid query that shows me 5 columns from these 4 tables:

As you can see the relationships that i'm trying to JOIN in the query are highlighted in differents colors.

The query i came out is:

SELECT c.CompanyName
, s.categoryname
, cprs.strenght
, s.parentid
, ss.Risklevel
, ss.ColumnType

FROM company_details AS c
JOIN category_master AS s ON s.id = c.categoryid
JOIN company_strenght AS cprs ON cprs.companyid = c.id
JOIN subsector_detasils AS ss ON s.parentid = ss.subcategoryid

But server gets unresponsive until i have to kill the process.
The issue is in the last line, if i remove it and only manage 3 tables i get a successful result but is not enough for what i want.

Problem description:
The company_details table is a list of companies. The id field is an unique ID that each company has.
We need to match that id with the companyid field in the company_strenght table so we can get the c.CompanyName, s.categoryname, cprs.strenght columns. All good so far.

Then we need to include in the results the category_master table which contains the category that each company belongs to. So we match s.id = c.categoryidfor that. Still no problem and i get a list of companies with the catergories they belong to and also their parent category id.

Finally here is the problem: Each company has a parent category which id is parentid in the category_master table (marked in green). This parentid field is the same than the subcategoryid field in the subsector_detasils table.
We need to match s.parentid = ss.subcategoryid so we can get ss.Risklevel, ss.ColumnType columns in the results. But as i said, it's not working since phpmyadmin just stops working and i have to kill the process manually.

Is there any other way to get around this problem? I'm running out of ideas...

Thanks in advance.

It's likely something more like this,but I'm not sure if you want to use INNER JOINs or not:

SELECT c.CompanyName
, s.categoryname
, cprs.strenght
, s.parentid
, ss.Risklevel
, ss.ColumnType

FROM (
       (
         company_details AS c INNER JOIN category_master AS s ON s.id = c.categoryid
       ) INNER JOIN company_strenght AS cprs ON cprs.companyid = c.id
     ) INNER JOIN subsector_detasils AS ss ON s.parentid = ss.subcategoryid

make sure you're sure it's what you want, and I would consider sorting it as well to make sure everything you're expecting is there...

Also, to make this more readable, you might consider using some pre-made VIEWs, using a CREATE <view-name> VIEW AS <one-inner-join>


sql script to export table's column data to another table's column Let's say , I have two table with same schema but different data .
Table_A and Table_B .

Table_A
--------
ID(p_key)     Number(p_key)         Column3     Column4
-----------------------------------------------------
ID1                1                  AAA         BBB
ID1                2                  CCC         DDD
ID2                1                  EEE         FFF
ID2                2                  GGG         HHH

-

Table_B
--------
ID(p_key)     Number(p_key)         Column3     Column4
-----------------------------------------------------
ID1                1                  AAA_1       BBB_1
ID1                2                  CCC_1       DDD_1
ID2                1                  EEE_1       FFF_1
ID2                2                  GGG_1       HHH_1

I want to export(overwrite) Table_B column3 data to Table_A column3 , where ID and Number Columns data are equal .
After executing of script , Table_A's data should be ,

 Table_A
    --------
    ID(p_key)     Number(p_key)         Column3     Column4
    -----------------------------------------------------
    ID1                1                  AAA_1        BBB
    ID1                2                  CCC_1        DDD
    ID2                1                  EEE_1        FFF
    ID2                2                  GGG_1        HHH

How can I make this using sql script only ?
I use MS SQL-Server 2008 R2 .

An update with a simple join will work.

CREATE TABLE Table_A
    (ID varchar(3) not null, Number int not null, Column3 varchar(5), Column4 varchar(5))
;

INSERT INTO Table_A
    (ID, Number, Column3, Column4)
VALUES
    ('ID1', 1, 'AAA', 'BBB'),
    ('ID1', 2, 'CCC', 'DDD'),
    ('ID2', 1, 'EEE', 'FFF'),
    ('ID2', 2, 'GGG', 'HHH')
;

CREATE TABLE Table_B
    (ID varchar(3) not null, Number int not null, Column3 varchar(5), Column4 varchar(5))
;

INSERT INTO Table_B
    (ID, Number, Column3, Column4)
VALUES
    ('ID1', 1, 'AAA_1', 'BBB_1'),
    ('ID1', 2, 'CCC_1', 'DDD_1'),
    ('ID2', 1, 'EEE_1', 'FFF_1'),
    ('ID2', 2, 'GGG_1', 'HHH_1')
;

alter table Table_A
add constraint pk_ID_NUM_A PRIMARY KEY (ID, Number)
go
alter table Table_B
add constraint pk_ID_NUM_B PRIMARY KEY (ID, Number)

select * from Table_A
select * from Table_B

Now run the update :

update Table_A
set Table_A.Column3 = Table_B.Column3
from Table_A inner join Table_B
on Table_A.ID = Table_B.ID
and Table_A.Number=Table_B.Number

Below is the expected result that you want :

select * from Table_A
select * from Table_B


Trigger on 2 tables : issue with INSERT and UPDATE This is related to my previous question:
Trigger to cascade updates to a second table.

In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields in metadonnees (formerly table2). Merged like this now:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
RETURNS trigger AS
$BODY$
begin

IF (TG_OP = 'INSERT') THEN
    insert into activite.metadonnees (gid, date_publication, contributor, code_tranche)
    VALUES (new.gid, current_date, current_user, NEW.code_tranche)
    ;
    return new ;

elseif (TG_OP = 'DELETE') THEN
DELETE from activite.metadonnees
where gid = old.gid ;
return old ;

elsif (TG_OP = 'UPDATE') THEN
UPDATE activite.metadonnees
SET (contributor, code_tranche) = (current_user, NEW.code_tranche)
where metadonnees.gid = new.gid
;
return new ;
end if ;

end;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER trg_tbl_emprise_metadonnees
AFTER INSERT OR DELETE OR UPDATE
ON activite.emprise
FOR EACH ROW
EXECUTE PROCEDURE activite.tbl_emprise_metadonnees();

Table definitions:

CREATE TABLE activite.emprise
(gid integer NOT NULL DEFAULT PRIMARY KEY nextval('activite.emprise_gid_seq'::regclass),
surface numeric(9,2),
annee integer,
ro character varying(50),
numope character varying(12),
typope character varying(25),
geom geometry(MultiPolygon,2154),
typemp character varying(30),
nomope character varying(80),
numoa character varying(20),
numprescr character varying(25),
tranche integer DEFAULT 1,
code_tranche character varying(15),
producteur character varying(15),
notice_rapport character varying(50)

CREATE TABLE activite.metadonnees
(gid integer NOT NULL PRIMARY KEY,
date_publication date,
"date_création" date,
"généalogie" character varying(250),
"résolution_spatiale" character varying(5),
responsable character varying(10),
restrictions character varying(100),
source character varying(15),
creator character varying(50),
publisher character varying(80),
identifier character varying(50),
title character varying(80),
subject character varying,
code_tranche character varying(15),
contributor character varying,
dates character varying,
type_donnees character,
format character varying,
language character varying,
coverage character varying

If I use AFTER INSERT OR DELETE OR UPDATE trigger then when a record is inserted into emprise, the field contributor is overwritten by the current_user value for all records.

If the trigger is limited to AFTER INSERT OR DELETE then after insertion everything goes well: the field contributor is not replaced for all records (as desired), just the record concerned in emprise.

In both cases, the contributor and code_tranche are updated if, after insertion, code_tranche value is changed in table emprise. I will conclude that UPDATE is the cause of this dysfunction but I am unable to find why.

Your trigger function should work and cannot cause the described problem on its own. Moreover, this does not make any sense at all:

  If the ON UPDATE trigger is active then when a record is inserted into
  emprise, the field contributor is overwritten by the current_user
  value for all records.

UPDATE triggers are not fired on INSERT. Maybe just wrong terms?

And you mentioned primary keys before, but I don't see them here. Anything else "simplified"? You need to be precise to pin down the problem ...

Only a couple of simplifications, optimizations and a minor fix:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
  RETURNS trigger AS
$func$
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN
      INSERT INTO activite.metadonnees (gid, date_publication, contributor, code_tranche)
      VALUES (NEW.gid, current_date, current_user, NEW.code_tranche);

   WHEN 'DELETE' THEN
      DELETE FROM activite.metadonnees
      WHERE  gid = OLD.gid

   WHEN 'UPDATE' THEN
      UPDATE activite.metadonnees
      SET   (gid, contributor, code_tranche) = (NEW.gid, current_user, NEW.code_tranche)
      WHERE  metadonnees.gid = OLD.gid
      AND   (gid, contributor, code_tranche)  -- only update if anything changes
            IS DISTINCT FROM (NEW.gid, current_user, NEW.code_tranche);
   ELSE
      -- do nothing (should not occur)
   END CASE;

   RETURN NULL;  -- for AFTER trigger
END
$func$  LANGUAGE plpgsql;

Note how I base the UPDATE on OLD.gid. It would be slightly incorrect to base it on NEW.gid and possibly miss changes to that column.

You can simplify RETURN because, quoting the manual:

  The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

And CASE is a bit shorter and faster.

The root of your problem is still burried elsewhere.

Aside from that, a clean solution would be to remove redundant storage (if at all possible). Use a FK constraint with ON UPDATE CASCADE ON DELETE CASCADE. Code examples:

Cascade deletes from multiple tables
How to implement a many-to-many relationship in PostgreSQL?

And store contributor and code_tranche in table emprise once per entity. Then you need no trigger on UPDATE or DELETE, just on INSERT. Or even merge the two tables (which seem to be in 1:1 relation). Then you need neither triggers nor FK constraints. NULL storage is cheap - in case you are worried about many null columns in the one table.

Do nullable columns occupy additional space in PostgreSQL?


SQL Server Licensing & Clustering for Availability Groups For SQL Server 2014, reading this MSDN link and Microsoft PDF file about licensing, created a couple of questions.

For Availability Groups do we need WSFC or FCI?
How are WSFC and FCI different?
If we only need 2 AG nodes, could SQL Server Standard Edition work?

In SQL Server 2012 and 2014, the Availability Groups feature requires an underlying WSFC (see this post for info on newer versions). You can use FCIs in combination, but it is not required (and in fact often overkill, depending on what you are trying to accomplish and whether you have underlying redundancy as well).
WSFC is the underlying Windows Server Failover Cluster. FCI is just the terminology for a single SQL Server failover cluster instance. An FCI depends on a WSFC, and can use 1-n nodes of the WSFC to provide failover scenarios for the clustered instance. Some good background here from Allan Hirt. Basically, an FCI protects a single, entire instance of SQL Server (when a failover happens, the entire instance moves to the next preferred owner in the WSFC). A WSFC protects an instance of Windows, and any SQL Server instances on it. An Availability Group uses the underlying WSFC to protect a group of databases.
In 2012 / 2014, Standard Edition does not support Availability Groups in any shape or form - Enterprise Edition is required. In SQL Server 2016 and above, there is "Basic Availability Group" for Standard Edition - 2 nodes only, 1 database per group, no formal listener support, no read-only secondaries, etc. Official Documentation...


how to change sybase sql default port Is there any option to change the sybase sql default port ?

Here I want to change the port number because we can't start two SAP instances at the same time on the same server?

Sybase ASE retrieves port numbers for the listeners from the interfaces file. On *nix systems, this is typically in the Sybase Home directory ($SYBASE/interfaces)

On Windows systems it's called interfaces.ini and can be found in %SYBASE%, which is likely to be c:\sybase (I think..been a while since I've run on Windows).

I believe you can launch the GUI interfaces editor through Sybase Central, if you are using that to manage your ASE databases.  dsedit and dscp are the two utilities that can be used to edit the interfaces file.

You can also manually edit the interfaces file using a text editor such as vi but be sure to pay attention to the format of the entries and permissions, so as not to accidentally muck it up.


writing a trigger in order to change Table Name automatically Imagine there are two databases named "DB1" and "DB2".
There are two tables on both databases which are the same.
I mean we have "Table1" in "DB1" and also "Table1" in "DB2" and the data is the same as well.
I need to write a trigger so that whenever i change "Table1"'s name on "DB1", the name be changed automatically by a trigger on "DB2".
thank you.

Sys.Trigger_event_types will have all the events that can be fired.

On checking, it has an event type called RENAME. You could use that write a DDL trigger, though this may not be ideal but serves your purpose.

alter trigger  tst
on database
for
RENAME
as
begin

declare @oldtblname sysname,@newtablename sysname
  SELECT
       @oldtblname= EVENTDATA().value
        ('(/EVENT_INSTANCE/Parameters/Param)[1]','nvarchar(max)') ;

          SELECT
       @newtablename= EVENTDATA().value
        ('(/EVENT_INSTANCE/Parameters/Param)[2]','nvarchar(max)') ;

exec otherdbname.dbo.sp_rename @oldtblname,@newtablename;

end

There are some catches, since this is database level scoped event, this will fire for all rename events, you may want to check event type and if it is only <ObjectType>TABLE</ObjectType>, then you may wish to continue.

Note:
I have  SQLServer 2012 and 2005 machines, this RENAME didn't worked on 2005 but worked on 2012. Not sure about 2008.


Postgres - returning custom values ie. "true" rather than "t" I am wondering if there is a way to show custom "values" in the output of a query purely for readability, similar to the AS statement for renaming column labels.

Seems unlikely but would be useful for cutting down a number of custom post-query formatting steps.

ie.

| type (varchar) | status (boolean) |
| A              | t                |
| B              | f                |

as

| type  | status |
| A     | true   |
| B     | false  |

or

| type  | status     |
| A     | collecting |
| B     | disabled   |

I am aware I could change the database to contain these values, but this is not possible in this case.

Thanks

You can use a CASE WHEN clause to custom the output :

SELECT CASE WHEN status THEN 'true'
            WHEN NOT status THEN 'false'
            ELSE 'uknknown'
       END AS status
FROM mytable;

If you want to change the default output of PostgreSQL in order to output 'true' instead of 't', I'm pretty sure it is not possible.


Review of Log Messages Database Schema so i have been tasked to design a log database and i would appreciate some
feedback about my design.
I have an application that consists of three basic parts:

A frontend
A backend
A low level component

Each part can create a log message which needs to be stored in a database.
The parts(front end, backend, low level component) that create the log messages should be uniquely identified.
At the same time when looking at a message it should be possible to see which part created the message.
Each message has a specific type assigned to it and the type can be one of the following destinct values

Error,
Warning,
Info,
Debug

The message itself should also be unique, it should have a text that says what is the problem
and possibly also a description with extra information about the problem and under which circumstances
it could have happened. In addition the time the message was created is very important. Because of the
low level component we need microsecond accuracy.
Examples
Message : Pump Failure
Description: The pump is not pumping enough oil. Check the amount of oil and also check the temperature of the system.
Finally there are some extra "requirements" that in my opinion could affect the design of the system:
The low level component produces a lot of messages in a short amount of time. This could lead to
the database reaching its storage limit relatively fast. In that case the older messages should be
deleted first. However there are  rules that need to be taken into consideration before deleting a message.
An info is less important than a warning and a warning is less important than an error.
Another rule is unless I have reached a specific threshold I am not allowed to delete messages
of a specific type e.g Only if have more than 500 errors am I allowed to start deleting the older
errors.
My current design is the following:
Message
    Id (PK)
    Name varchar
    MessageTypeId(FK)
    Description varchar

MessageType
    Id (PK)
    Name Varchar

Sender
    Id (PK)
    Name Varchar

MessagesLog
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

However taking into consideration these extra requirements and thinking that I will need to do
a lot of checking on an application level if certain criteria are fulfilled before i delete a record
from the database, i thought about creating a separate table for each message type:
Message
    Id (PK)
    Name varchar
    Description

Sender
    Id (PK)
    Name Varchar

MessagesLogError
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

MessagesLogWarning
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

MessagesLogInfo
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

MessagesLogDebug
    Id (PK)
    MessageId (FK)
    SenderId (FK)
    Date BigInt

What do you think?

First, I love that you have isolated your actual messages into it's own table, that was going to be my first suggestion but you had already gotten there.
Otherwise, I think your first design is good.  By keeping the message log itself (the meta data) isolated from the message body you will (with proper indexes) be able to find messages very quickly.
My only word of advice for you would be to do the delete check as part of the transaction that is writing the message (and to make that a small number).  This will make the cleanup action part of the message writing logic and keep things moving along quickly.
Something like:
Delete TOP (100) old messages that satisfy criteria
Insert new message


How do I simultaneously update unique keys in PostgreSQL? CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE
);

I have the data
 id | name | ordinal
----+------+---------
  1 | A    |       1
  2 | B    |       2
  3 | C    |       3

I would like to update it to
 id | name | ordinal
----+------+---------
  1 | A    |       3
  2 | B    |       2
  3 | C    |       1

Touching records as little as possible (i.e. don't rewrite the entire record set, don't kick off unnecessary triggers), what's the generally applicable approach to updating ordinal to be the target values?
A vanilla update just gives me constraint violations, even if it happens in a single statement.
And dropping and recreating the unique constraint is expensive and poor concurrency.
This seems like a common enough problem that there ought to be a good way to do this, that I just can't think of.

Define the constraint as deferrable:
CREATE TABLE widget (
  id serial PRIMARY KEY,
  name text NOT NULL,
  ordinal int NOT NULL UNIQUE DEFERRABLE
);

Then you can update it in one statement:
update widget
  set ordinal = t.new_ordinal
from (
  values (1, 3), (3,1)
) as t(id, new_ordinal)
where t.id = widget.id


MySQL: Why is this UPDATE query so incredibly slow? So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts table, but only update the records which are needed and keep track of which  ones.

This is the code I am using to find changes :

SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price

This is the code I am using to update :

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1

When the two pieces of code are run separately they complete in less than a second each. However when combined like below, the query runs for HOURS !

UPDATE ActiveProducts ap INNER JOIN XMLImport xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1
WHERE ap.sku IN (
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
);

Below is the structure

ActiveProducts.Product_id - PrimeKey
ActiveProducts.name - Index
ActiveProducts.sku - Index

XMLImport.Product_id - PrimeKey
XMLImport.name - Index
XMLImport.sku - Index

Server version: 5.5.44-MariaDB MariaDB Server

Tables: InnoDB

Why don't you just build a temp table and use the results to update. This is bad form. It's got to build the Subquery first, then assemble that in the WHERE Clause, then it's going to update every record to find a match.

Instead just INNER JOIN to a temp table that does the first part, or a CTE if you're using SQL Server and can't have a multi-step process.

WITH SkuList AS
(
SELECT DISTINCT sku FROM (
  SELECT name,size,colour,sku,quantity,price FROM ActiveProducts
  UNION ALL
  SELECT name,size,colour,sku,quantity,price FROM XMLImport
) tbl
GROUP BY name,size,colour,sku,quantity,price
HAVING count(*) = 1
ORDER BY name,size,colour,sku,quantity,price
)
UPDATE ActiveProducts ap INNER JOIN SkuList xml ON ap.sku = xml.sku
SET
  ap.name = xml.name,
  ap.size = xml.size,
  ap.colour = xml.colour,
  ap.instock = xml.instock,
  ap.quantity = xml.quantity,
  ap.price = xml.price,
  ap.updated = 1


How to list all functions with arguments that a role is allowed to execute in Postgres? I found how to list tables a role has permissions on. I found how to list all functions with arguments.

My question is: How to list all functions with arguments a role is allowed to execute?

You can use the has_function_privilege function for that:

SELECT p.pronamespace::regnamespace as schema,
       p.proname||'('||pg_get_function_arguments(p.oid)||')' as function
FROM pg_proc p
WHERE has_function_privilege(p.oid, 'execute');
  and p.pronamespace <> 'pg_catalog'::regnamespace;


Don't RESEED tables on Query Store Clean We are using Query Store. And now we have solved one problem with it by cleaning the Query Store every night. And when you clean the Query Store, the Query Store tables are being RESEEDed :-(

Do you know how clean the Query Store without RESEEDing the tables?

Assuming you're using the command ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR to "clean the query store," I decided to see what queries are run behind the scenes when that command executes.

To that end, I set up a basic Extended Events session by using the "Query Detail Tracking" XE template that's included with SSMS.  This captures several events that should cover most queries running on a system:

error_reported
module_end
rpc_completed
sp_statement_completed
sql_batch_completed
sql_statement_completed

By default it excludes queries running in the context of the system databases (database_id > 4), which is fine for this test since query store lives within the user database where it's enabled.

After starting that XE session, I ran the ALTER DATABASE...SET QUERY_STORE CLEAR command, and the following queries were picked up by the session:

TRUNCATE table sys.plan_persist_runtime_stats;
TRUNCATE table sys.plan_persist_runtime_stats_interval;
TRUNCATE table sys.plan_persist_plan;
TRUNCATE table sys.plan_persist_query;
TRUNCATE table sys.plan_persist_query_text;

Adding the optional "ALL" parameter after "CLEAR" ran this additional statement:

TRUNCATE table sys.plan_persist_context_settings;

You may notice that these are the base tables for the Query Store catalog views.

TRUNCATE TABLE resets identity values:

  If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

So this behavior is "by design" - you can't depend on the identity values in the query store tables across "query store clear" operations.

What problem are you solving by clearing the query store?  Perhaps we could suggest a different option if you provide that context in a new question.

Some potential options for removing some queries without truncating the tables are found here:

Best Practice with the Query Store - Keep the most relevant data in Query Store

Configure time-based policy to activate auto-cleanup
Activate size-based cleanup policy
Configure Query Capture Mode to Auto

The first two options cause rows in the Query Store to be deleted by id based on their age and / or how expensive the queries were.

The second option limits the amount of data being added to the Query Store in the first place.


One reason could be somebody changing options or running sp_configure. That would be logged in your error log.

Please read this article: Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

  using sp_configure to change a configuration value causes SQL Server
  to issue a DBCC FREEPROCCACHE statement.

and

  The same behavior will occur if you use a GUI such as SQL Server
  Enterprise Manager that issues an sp_configure call that changes a
  parameter.

The Plan Caching and Recompilation in SQL Server 2012 white paper gives some other possibilities.

  Flush Entire Plan Cache The following operations flush the entire plan cache, and therefore, cause fresh compilations of batches that
  are submitted the first time afterwards:

  Detaching a database
  Upgrading a database to a later database compatibility level
  Restoring a database
  DBCC FREEPROCCACHE command
  RECONFIGURE command (many of the options to this command cause a flush)
  ALTER DATABASE … MODIFY FILEGROUP command
  Modifying a collation using ALTER DATABASE … COLLATE command

  Flush Database-Related Entries in Plan Cache The following operations flush the plan cache entries that refer to a particular
  database, and cause fresh compilations afterwards.

  DBCC FLUSHPROCINDB command
  ALTER DATABASE … MODIFY NAME = command
  ALTER DATABASE … SET ONLINE command
  ALTER DATABASE … SET OFFLINE command
  ALTER DATABASE … SET EMERGENCY command
  DROP DATABASE command
  When a database auto-closes

Lots of these events should be in your log files.


Postgresql database backup (and restore) from two servers to third storage place I have two server machines with PostgreSQL: (A) is version 9.0.15 and (B) version 9.2.
I wish to make backups of the databases stored in (A) and (B) - and be able to do restore if needed too! - to a 'storage' machine (C) where I have postgresql-commons clients installed (9.3).

Which is the cleanest/better way to obtain my goal?

I've made scripts on (C) to use pg_dump to make local backups from (A) and (B), but being a newer version I've problems with restore, also when using Plain sql format.

Do I need to install clients pg_dump/restore for 9.0 and 9.2 version on the client machine (C) and if so, how? thanks.

I would run the backup jobs on A and B at specified times using the local native pg_dump, then push them to C for storage.

sudo su -c 'pg_dump -f ~/backupfile.sql -C "MyDB"' myuserid
scp ~/backupfile.sql myuserid@cmachine.mydomain.com:

Restoring from the backup file can be done with:

psql -d <database> -f backup-file.sql


Restricting database access per IP addresses I have filtered IP addresses to an access DB server like this:

Set the sqlnet.ora

tcp.validnode_checking = YES
tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186)

But in my ORACLE_HOME, there are more than 1 Database, let say DB WORKSHOP and DB COURSE

I want to ask about connecting to database for each DB, example:

DB WORKSHOP only can be access from 192.168.100.130
DB COURSE only can be access from 192.168.100.186

What can I do to make it?

I think the only pure Oracle way to do this would be to run 2 separate listeners on different ports out of 2 different ORACLE_HOMEs, rather than just using the one.

A much more sane way of approaching this would be to use other security measures... Separate Usernames and passwords are quite common for this task - I guess you must be using LDAP or something that prevents this. Another option would be a login trigger on each database that checks the remote IP and disconnects the session if it's not come from the correct machine.

select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;

... will give you the remote IP address.

A trigger such as:

CREATE OR REPLACE TRIGGER DENY_LOGIN
AFTER LOGON ON DATABASE
DECLARE
  foo varchar2(128);
BEGIN

  IF ( sys_context('userenv','ip_address') <> '192.168.100.130' )
  THEN
    raise_application_error( -20001, 'Connection not authorised' );
  END IF;

EXCEPTION
WHEN OTHERS THEN
  RAISE;

END;

... will do the job for DB WORKSHOP.

(Untested, but should be OK)

Probably best to add a clause that allows DBAs to login from localhost too, or just exclude them completely :)


Trace To the Error Log Thomas LaRock (SQL Rockstar) put up an article on how to write executed commands directly to the SQL Server Error Log without having to use Profiler (or SQL Trace).  The article/video can be found here.

My question is, would this be advisable to anything beyond quick and limited troubleshooting?  In other words, I can't imagine it wouldn't impact performance by keeping trace flags 4032 and 3605 set for an extended period of time.

Is it a fair statement that the instance would suffer by having all the I/O to the error log for every single statement on the instance?

I think the article is useful in a very limited scenario:

You are on a local or otherwise isolated machine.
You need to do this for a very short burst of activity.
You have no access to Profiler (not my first choice for tracing anyway), no idea how to construct a server-side trace, and no access to Books Online so you can learn the syntax (you don't need Profiler to set up a trace).
You have control over, or at least cursory knowledge of, the set of statements that might be executed against your instance for the duration of your "trace."
You have a good idea of how much space is on your C drive...

Without those disclaimers in place, I'm actually quite nervous that someone is going to skim the article, not read the comments, put this into production, and forget about it until - well, you can imagine the handful of scenarios that could result.


Sessionizing Web Log Data using MySQL (perhaps by simulating window functions) I have a large MySQL Table containing about 15 Million rows of web log data. Depending on the IP, useragent and site_id (different sections on the website) I want to create user sessions. The session delimiter should be 30 minutes. So if a unique user (same IP and useragent) is inactive for 30 minutes or if he visits a different site area (2, 3, or 4) a new session starts.

I found a very good blog entry, that describes the general procedure: http://randyzwitch.com/sessionizing-log-data-sql/ But I can't implement this 1:1 because MySQL doesn't support window functions and I'm not proficient enough with MySQL to figure it out on my own, e.g. with GROUP_CONCAT().

Here is anonymized example data on sqlfiddle: http://www.sqlfiddle.com/#!9/a8b1f/1

It would be really great if you could help me.

I already posted this question on Stack Overflow.

You've provided your own answer - MySQL doesn't have window-functions (AKA analytic functions). My advice to you would be to switch to PostgreSQL for that reason alone.

Also, PostgreSQL supports SET operators, CHECK constraints, GIS, calculated fields (although MariaDB does support these),  and CTEs (Common Table Expressions, AKA the WITH clause) - although see Justin Swanhart's answer here.


MySQL high CPU usage under medium load I'm managing a Magento server with MySQL installed in it. 16GB RAM 8 core vCPU (Digitalocean).
We're having a problem with the site becoming a bit sluggish when under medium to heavy load. Under low to none, MySQL uses around 25% to 60% of CPU (according to HTOP). Under medium to heavy load MySQL takes up 150% of CPU usage (again, according to HTOP). On the graph CPU usage on all cores is stuck at 100% during medium usage of the website.
My MySQL configuration is the default, a performance tuning was never done. I'm working on configuring PHP and Elasticsearch memory/CPU usage, but I need help with MySQL configuration since I never dealt with public facing servers (web store).
This is what mysqltuner says:
[!!] Maximum reached memory usage: 154.1G (983.67% of installed RAM)
[!!] Maximum possible memory usage: 153.1G (977.27% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Highest connection usage: 100%  (152/151)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 2B selects)
[!!] Joins performed without indexes: 600679
[!!] Table cache hit rate: 0% (2K open / 3M opened)
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Read Key buffer hit rate: 90.7% (378 cached / 35 reads)
[!!] InnoDB buffer pool / data size: 1.0G/1.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal to 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[!!] InnoDB Write Log efficiency: 8.97% (62690490 hits/ 699060007 total)

General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (2419)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    table_open_cache (> 2419)
    innodb_buffer_pool_size (>= 1.2G) if possible.
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

Significant problem 1 - table_open_cache
Table cache hit rate: 0% is significantly bad. Identify how many tables are in Magento and increase  the table_open_cache to higher than that.
open_files_limit needs to be higher than this number however (as it includes connections also). High number like 64k are ok.
After that enable your slow_query_log, long_query_time and start identifying slow queries.
As you do have 16G of RAM setting innodb buffer pool size to 4G would be prudent as notably your data size is only 1.2G.
Examine performance again after these changes.


How to stabilize performance on frequently updated table in PostgreSQL I'm trying to find-out a way how to prevent performance degradation in my PostgreSQL database.

The table is updated every minute with 100,000 - 200,000 INSERTs. Every 5 minutes the table is updated with the same scale of aggregated UPDATE and a number of DELETEs.  Aggregations and DELETEs are done in a way to keep the size of the table around 10 million records.

Apparently updates are too frequent so AUTO VACUUM cannot be completed. I started to run VACUUM after each DELETE series. That improved behavior a lot.

I've a suspicion that periodic reindexing would help but haven't verified it yet. Actually it's very suspicious that PostgreSQL has a dedicated command for reindexing.

I have played with different fillfactor for the indexes, with no visible difference.

What else should I consider to make keep performance stable? Let's say that slow but stable over time is better than sometimes fast and sometimes very slow.

UPD: vacuum analyzed after each delete + reindex every 6 hours solved the issue.

Some things depend on your PostgreSQL version.  On some older versions pay attention to fsm settings to make sure it can track free space in your tables for re-use.  You might also look at scheduling a periodic cluster if you can afford the downtime.  If not, pay attention to the indexes.


MS SQL Server 2008R2, "Login failed for user" from time to time, IIS 7 , NET 4.0 I'm fighting with strange problem that that most probably is connected with Windows / MS SQL Server security/privileges setup. When external app (that runs with administrator privileges) polls the IIS server(it does login to database with already opened connection, checks for data) and when there is data to process(with the same connection when it then tries to get data) fails from time to time with this error:        Cannot open database "TheDatabase" requested by the login. The login failed. Login failed for user 'DatabaseUser'.
        Even worse, when this happens - every activity that involves database fails with same error message. So for a few minutes the database and thus whole website is down. But I'm not able to recreate this no matter what I do and I'm only able to see this in exception logs. Database user,that public login is mapped to, is 'dbowner' and nothing points to why it happens. This issue seem to appear after update of website code - ie this happened due to some particular changes in either Windows security or SQL Server config or code, but review of changes in sources gives no clues as everything seems 100% unrelated to this. Thanks in advance for helping with this.

The clue is this

  Cannot open database "TheDatabase" requested by the login

This means that some connection string has either a non-existent database mentioned, or the login used does not have access. It is also possible that no database is mentioned, and the default database for the login is missing/no permission.

One of these 3 options is usually the answer...

Edit: you don't have auto-close on the database, do you?


Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError) I am using mysql innodb cluster. Connect to my instance use \c root@localhost:3306.
The error message pops out when I issue var cluster = dba.getCluster('testCluster').

I've already created the cluster in last session. But when I reconnect it does not work anymore.

The only thing I changed is hostname from localhost to db005 and restart mysql server. Don't even know if it has something to do with my problem. Anybody has idea what happened?

Did some searches on google. Seems this is the only resource from mysql forum. But still not able to solve my problem.
https://forums.mysql.com/read.php?177,653826,657465#msg-657465

Thx!

It seems the node from the one you are trying to use the command is not part of the Cluster. What you can do is to try to get the status from another node.

If this is not possible, then try to execute:

  mysql-js> var cluster = dba.rebootClusterFromCompleteOutage();

Anyway, check the follow article: https://mysqlserverteam.com/innodb-cluster-in-opc-part2/


How to PUSH data between a Local DB Server to Cloud DB Server? I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or VPN).

May I request you to please advise a solution, to achieve this data transfer push from Local DB Server to Cloud DB Server.

I will elaborate the scenario over here, what we are trying to achieve.

SCENARIO

My client has an ETL application developed in .NET which uses SQL Server as a backend.

They have their Live SQL Server hosted on a cloud provider (not Microsoft Azure)

They are opening up a new branch which does not have a leased line, since the area is on outskirts of the city. This new branch will have almost 1500 users who will make entries to a locally hosted application which will use local DB Server.

They have 2 SQL Servers:

SQL Server 2014 Web Edition Database on Cloud (not Azure)
SQL Server 2014 Standard Edition with same Application database on Local Premises

They need to push the data entries inserted in local DB Server Database named as LabProdData to the Cloud DB Server Database LabProdData, so all other branches can refer to this branch data, with a certain delay of 30 minutes or less.

What are the options to implement this scenario?

SOLUTION PROPOSED

I have proposed a solution of ETL creation to open up a SQL Port on the Cloud DB server to facilitate communication to Local DB Server and to push data to the Cloud DB Server
But, this will be quite hectic to develop multiple ETL's since the number of tables are more.
Also, the Deletion and Updation of Data needs to be tracked and Synced, which will add up complexity

My client is bit hesitant about opening the port on Cloud DB Server, since this can be vulnerable to threats.

REQUEST SOLUTION

My doubts are:

Can we use SQL Server Replication to replicate the data between Local SQL Server and Cloud DB Server, with no Active Directory or no common domain?
If yes, how do we implement this scenario using SQL Server Replication?
Is there any other way to implement this scenario, other than Replication? Can you please suggest.
Can Replication or Database Mirroring help here
Can SQL Server Replication support this with 2 DB Servers of same version but one is Web Edition and other is Standard. We need to push data from Standard Edition to Web Edition.
How can the communication be implemented between both these servers? Note: Network Domain or Active Directory do not exists.

Please do let me know if you need any further details to understand the problem statement.
Eagerly awaiting for your reply.

I realize this is an old question but may come in handy for others facing similar dilemma.

Your client is correct. Do no open up ports to the world, as this is will create a security risk. You will want a site-to-site VPN between your back offices and "the cloud". Once this is implemented the "the cloud" SQL will be accessible as if it was in your LAN.


Migrating SQL Server 2000 to 2008/14 - Stored procedures compatibility? I am planning migration from old SQL server 2000 db to 2014 (through 2008 extra step). I Wonder if stored procedures will work with no tweaks or arrangements are required

You should check breaking changes.

you can use upgrade advisor or

dbatools - Test-DbaMigrationConstraint

  This function only validate SQL Server 2008 versions or higher.

I have written an extensive list of things you should check when you migrate from 2000 to higher versions


3nf but waste more storage space I have a table which has three columns: account, idNumber, and position. The account and idNumber are unique.

Here is my thought to analyze and I don't know whether it is right or not.

account is primary key and account ->idNumber, account->position,
idNumber->account, idNumber->position

But I think that the idNumber->position dependency violates the Third Normal Form (3NF) rules (I am not sure.)

So, I decomposed it into two tables: one has account and idNumber, the other has account and position.  But this seems to waste storage space with one more account column.

So, I am confused - is the method I used correct?

If it's a table of 3 columns, you're probably overthinking this.  Strict 3NF is all well and good, but when you're talking about possibly having two tables just to follow it precisely, it's time to step away from the table, nobody's getting hurt here...

The intent of 3NF is that each table should contain unique keys, and all of the columns should describe those keys.  Placing data about a person and that person's workplace in one table doesn't make sense - you're really describing two things, so you should separate them.

In this case, it sounds like (and it's not totally clear, but I'm guessing) account and idNumber are both effectively keys.  If they're both unique in the table, with a 1:1 relationship, then they're both keys, and even if one of them is the primary key, they both serve that purpose.  So why not keep them in the same table?  You're not describing two different things here, are you?  It sounds like you're describing the same thing in two different ways.

Now, the fact that you have two unique IDs for the same records begs the question as to why.  Are these IDs from two different groups of [other] tables?  If that's the case, then you may need to have an account-idnumber only relationship stored in a table in order to more easily convert between them.  If that's not the case, do you really need both of these columns at all?


Does existing msrepl_tran_version column need to be removed when re-creating replication? What is the process for removing a replication and then re-creating the replication. I know the process to set up replication. However, If i wanted to take a publisher database copy it and restore it and create another publication and replication how would I do that?

Would I need to remove the previous mesrepl_tran_version column from all the tables???

The type of replication that I will be re-creating is Transactional Replication with updateable subscribers.

Additionally, After I restore the database and create the publication do I need to let the snapshot complete and then back up that database and restore on subscribers and create the subscription

or

when I restore the database for the publisher can I also restore it on the subscriber?

Help will be greatly appreciated!

Refer to : Why does replication add a column to replicated tables; will it be removed if the table isn't published?

If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

you can drop the columns after you restore the database (if you want). Nothing harm in keeping them as well.

-- this script will generate drop statements.
-- copy the generated statements to DROP columns related to T-rep msrepl_tran_version
-- First drop constraints
SELECT 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + DC.NAME + ''') and Type = ''D'')
            ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.NAME + '] DROP CONSTRAINT [' + DC.NAME + ']'
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN sys.default_constraints DC ON SO.ID = DC.Parent_object_id
    AND SC.colid = DC.Parent_column_id
WHERE SO.XTYPE = 'U'
    AND SC.NAME = 'msrepl_tran_version'

UNION

SELECT 'IF EXISTS (SELECT * FROM dbo.syscolumns where id = OBJECT_ID(N''' + SO.NAME + ''') and Name = ''msrepl_tran_version'')
           ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.NAME + '] DROP COLUMN [msrepl_tran_version]  '
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN sys.default_constraints DC ON SO.ID = DC.Parent_object_id
    AND SC.colid = DC.Parent_column_id
WHERE SO.XTYPE = 'U'
    AND SC.NAME = 'msrepl_tran_version'
ORDER BY 1

Also, refer to How to cleanup Replication Bits


Get identity value of all rows inserted I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement?

Example

insert into DestinationTbl select * from SourceTbl where [col1]='xyz'

Above statement may insert more than one rows into a table. So i want identity value of all the rows which are inserted. How it can be done?

You can use the output clause to throw the inserted rows into a table variable. Then you'll be able to see all of the identity values.

Link for the output clause:
http://technet.microsoft.com/en-us/library/ms177564.aspx


Automatically create linked row in a different table What I mean is that for example if I have a table user with id name prefId, and a second table preferences with id etc..., is it possible that when I add a row to user, it would automatically create a row in preferences and set the row's id to prefId? Should I create the preferences row first and manually put its id? And is this a good practice?

Use triggers, see here

Create an insert trigger on the user table. In your trigger, create a record on the preferences table, get its id and store it in prefId on user.


Remove db_denydatawriter, db_denydatareader role from admin user I have an AWS SQL Server instance, and I have accidentally given my user (and the only user I have access to) db_denydatawriter and db_denydatareader role on our primary database, which stops me from making any other changes to the db permissions to remove them.

Remembering this an AWS db instance, and the user I have locked out is the only user I have access to.

Surely it can't be this simple to ruin a database?

jcdbadmin is the user in question

If you need to reset your permissions for the master user to the defaults (dbo access in each database), you can reset the master user password from the AWS console and Apply Immediately to have changes take effect.

Here are the steps to do this:

Go to the AWS Console
Instance Actions -> Modify
Under Settings type in a new password for the master password.
Select Apply Immediately


Multiple versions of SQL Server on the same development machine I am consolidating some SQL Servers to simplify administration. I am thinking about a new VM with SQL Server 2005/2008R2/2012 each as a separate named instance on the same machine. This machine will only be used in a 'sandbox' environment primarily to test restores of our backups. No applications will be hitting this.

Am I likely to run into any issues with multiple versions? I remember running into issues years ago with SQL 2000/2005 on the same box. Do you folks use separate machines for restore testing?

No, shouldn't be any issues; I have a VM right now with 2000, 2005, 2008, 2008 R2, 2012 and 2014 and it works just fine.

I do suggest to observe the following:

install the oldest version first, and get it completely patched before moving on to the next.
use a very consistent and intuitive naming scheme for each instance (e.g. .\SQL2000, .\SQL2005, etc). Also don't install a default instance (because it can't self-document what version it is, its purpose, etc).
don't install multiple versions of the client tools, except the case where you need to manage SSIS packages in multiple versions. For just about everything else, you can manage all down-level versions with the 2012 client tools. If you plan to use Visual Studio (any version), don't install it in the VM, use it in the host. Different versions of Visual Studio don't always play well with different versions of SQL Server (particularly client tools).
make sure to not allow the default startup behavior, where all instances will start automatically (you probably only want a specific version to be running and using resources at any given time).
use the same sa password, SQL Server service account, SQL Agent account etc. on all instances, and make sure you add your own account as an admin on all instances. The last thing you want is to get locked out of one instance because it has a different password or to suffer some issue that you can't tell is account-related or version-related.
don't create databases on version x and expect to backup/restore them on x-n. Backups are not backward compatible (regardless of compatibility level), only forward.
be mindful of recovery models, log auto-growth settings, and large databases. For example you don't want to install the AdventureWorks DW database on every single instance - this takes up valuable space and you will soon consume your entire drive. Keep those databases handy (like on an external drive) but only attach/restore them when you need them on a specific version.


Error when installing SQL Management Studio 2014 from product installer My Inno Setup 5 installer internally calls SQLManagementStudio_x86_ENU.exe (2014 SP1), and this consistently fails.

However, when I open Command Prompt in administrator mode and execute SQLManagementStudio_x86_ENU.exe directly, passing in the exact same arguments that are used by the Inno Setup 5 installer, SSMS installation is consistently successful.

I am updating the installer for an older product. One of the components deployed by the installer used to be SQL Server Management Studio 2005. I am updating that installer to instead install SQL Server Management Studio 2014. The installer is produced in Inno Setup 5.

I am executing on the 2014 SP1 version of SQLManagementStudio_x86_ENU.exe.

I am using the following arguments: /ACTION=install /QS /IACCEPTSQLSERVERLICENSETERMS /FEATURES=ADV_SSMS,BC,Conn,SDK,SNAC_SDK,Tools /UpdateEnabled=False

I am installing to a virtual machine running Windows 7 SP1 32-bit. I am on a domain account, with local administrator rights on the current machine. The following versions of the .NET Framework are installed:

v4.5.51209 (Full)
v4.5.52109 (Client)
v3.5.30729.5420
v3.0.30729.5420
v2.0.50727.5420

The relevant sample from the end of my SqlSetup.log file is:

02/22/2016 17:43:24.217 Saved .Net security policy file
02/22/2016 17:43:24.218 Attempting to release setup mutex
02/22/2016 17:43:24.219 Setup mutex has been released
02/22/2016 17:43:24.220 SQM key not found
02/22/2016 17:43:24.220 Setup closed with exit code: 0x84C40013
02/22/2016 17:43:24.221 ======================================================================

I tried pasting the Full Output from my SqlSetup.log here. However, I couldn't include it all as this took me over the content limit for how much text can be in a single post.

Relevant section from my Inno Setup 5 log below.

Note that this section includes custom logging information I have added myself so as to trace when registry keys are being analyzed, and what the results are. At present, the CheckSSMS2014SP1x86Installed function is behaving correctly as it is identifying that SSMS 2014 has not been installed.

2016-02-22 17:42:25.820   InstallSSMS2014SP1x86
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck: Checking if registry key exists: HKLM32\SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck: Key does not exist!
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck: Returning false
2016-02-22 17:42:25.820   InstallSSMS2014SP1x86: Returning true
2016-02-22 17:42:25.820   -- Run entry --
2016-02-22 17:42:25.820   Run as: Current user
2016-02-22 17:42:25.820   Type: Exec
2016-02-22 17:42:25.820   Filename: C:\Program Files\Application Directory\Components\MS SQL Server\SQL Server 2014\SP1\Express\x86\SQLManagementStudio_x86_ENU.exe
2016-02-22 17:42:25.820   Parameters: /ACTION=install /QS /IACCEPTSQLSERVERLICENSETERMS /FEATURES=ADV_SSMS,BC,Conn,SDK,SNAC_SDK,Tools /UpdateEnabled=False
2016-02-22 17:43:24.280   Process exit code: 2227044361
2016-02-22 17:43:24.280   CheckSSMS2014SP1x86Installed
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck: Checking if registry key exists: HKLM32\SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck: Key does not exist!
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck: Returning false
2016-02-22 17:43:24.280   Exception message:
2016-02-22 17:43:24.280   Message box (OK):
                          Internal error: Expression error 'Runtime Error (at 57:279):

                          Problem installing SQL Server Management Studio 2014 SP1. Unable to continue with installation. Please check your computer specifications meet minimum requirements.'

Finally worked it out.

My issue was actually two issues. Neither one was giving good error feedback. I had to stumble on fixing them both to get the installer to work as intended.

Firstly, I reduced the list of features being installed.

/ACTION=install /QS /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SSMS /UpdateEnabled=False

Secondly, I changed the length of the path leading to the SSMS installer at the time it was executed.

The previous execution path was:

C:\Program Files\Application Directory\Components\MS SQL Server\SQL Server 2014\SP1\Express\x86\SQLManagementStudio_x86_ENU.exe

The new execution path is:

C:\Program Files\Application Directory\Components\MSSQL2014SP1EXPx86\SQLManagementStudio_x86_ENU.exe

With both changes in place, the automated installation works as intended.


Give access to view or table? I want to give an external user (new login) read access to part of the data model from my Database. The user will copy the data model and content from my Database, and then integrate that into their own database.

Should I give the user a read access to the actual tables, or should I create views for the Tables they need to see?
If a View is the way to go, can I put unrelated tables in the same View?

In almost ALL cases, YES, use a view. Then you can have multiple joined tables, as may be required, whereto you would not have to give any access


Using @@ROWCOUNT in multi-operation batch to check either a failure or a success of the whole transaction I have the following script defined with hopefully explanations in comments to explain rationale in the right context. The questions are basically:

Is the following usage of @@ROWCOUNT in conjunction with XACT_ABORT OK to handle such a multi-operation transaction where if either one (in this case) fails, all should be rolled back. It looks OK, but I wonder if there are edge cases that should be accounted for.
Is there a handier way of checking existence than that construct? I suppose if there is a unique constraint in Table1Value, there shouldn't be a problem.

The query:

SET XACT_ABORT, NOCOUNT ON;
DECLARE @ROWCOUNT AS INT;
BEGIN TRANSACTION;

-- Insert this if it doesn't exist already.
INSERT INTO Table1
(
   Table1Value
)
SELECT
   @table1Value
WHERE NOT EXISTS
(
    SELECT 1
FROM
    Table1
WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
);

-- Update this table if insertion to the previous one succeeded. That is,
-- the @@ROWCOUNT in the WHERE conditional is more than zero. It's
-- OK if the the value changed was the same as before.
UPDATE Table2
SET
    Timestamp = GETUTCDATE(),
    VersionCounter = VersionCounter + 1
WHERE
    Table1Value = @table1Value AND @table1Value IS NOT NULL
    AND VersionCounter = @versionCounter AND @versionCounter IS NOT NULL
    AND @@ROWCOUNT > 0;

-- The idea here is that the transaction will be committed
-- if both of the previous operations succeeded. Otherwise it
-- will be rolled back. Either way, an explicit return value is
-- used to indicate either a success or a failure.
SET @ROWCOUNT = @@ROWCOUNT;
IF @ROWCOUNT = 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

-- Interpreted as TRUE if this is more than zero. FALSE otherwise.
SELECT @ROWCOUNT;

To my surprise it looks like this works.  I wasn't aware that during the next query @@rowcount persisted from the previous query.  I don't know of any particular cases where it wouldn't but I would still probably change it to this just in case.

SET XACT_ABORT, NOCOUNT ON;
DECLARE @ROWCOUNT AS INT;
BEGIN TRANSACTION;

-- Insert this if it doesn't exist already.
INSERT INTO Table1
(
   Table1Value
)
SELECT
   @table1Value
WHERE NOT EXISTS
(
    SELECT 1
FROM
    Table1
WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
);

-- Update this table if insertion to the previous one succeeded. That is,
-- the @@ROWCOUNT in the WHERE conditional is more than zero. It's
-- OK if the the value changed was the same as before.
SET @ROWCOUNT = @@ROWCOUNT;

IF @ROWCOUNT > 0
    UPDATE Table2
    SET
        Timestamp = GETUTCDATE(),
        VersionCounter = VersionCounter + 1
    WHERE
        Table1Value = @table1Value AND @table1Value IS NOT NULL
        AND VersionCounter = @versionCounter AND @versionCounter IS NOT NULL;

-- The idea here is that the transaction will be committed
-- if both of the previous operations succeeded. Otherwise it
-- will be rolled back. Either way, an explicit return value is
-- used to indicate either a success or a failure.

SET @ROWCOUNT = @@ROWCOUNT;
IF @ROWCOUNT = 0
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

-- Interpreted as TRUE if this is more than zero. FALSE otherwise.
SELECT @ROWCOUNT;


Nonclustered index is faster than clustered index? Both tables have same structure and 19972 rows in each table.
for practicing indexing, i created both tables having same structure and created

clustered index on persontb(BusinessEntityID)

and

nonclustered index on Persontb_NC(BusinessEntityId)

and table structure

BusinessEntityID int
FirstName varchar(100)
LastName  varchar(100)

 -- Nonclusted key on businessentityid takes 38%
SELECT  BusinessEntityId from Persontb_NC
WHERE businessentityid BETWEEN 400 AND 4000

-- CLustered key businessentityid takes 62%
SELECT BusinessEntityId  from persontb
WHERE businessentityid BETWEEN 400 AND 4000

Why clustered index takes 62% and non clustered 38%?

Yes the clustered index has fewer rows per page than the non clustered index as the leaf pages of the clustered index must store the values for the other two columns (FirstName and LastName).

The leaf pages of the NCI store only the BusinessEntityId values and a row locator (RID if the table is a heap or the CI key otherwise).

So the estimated costs reflect the greater number of reads and IO requirement.

If you were to declare the NCI as

nonclustered index on Persontb_NC(BusinessEntityId) INCLUDE (FirstName, LastName)

then it would be similar to the clustered index.


Find and replace database name in views and stored procedures I'm in a bit of a predicament. There are over 1,000 views and stored procedures in a handful of databases that are hard-coded to go after another database.

In my example, the database names in SQL Server are HOST_1 and LOYALTY_1. The views and procedures are trying to go after HOST_PROD and LOYALTY_PROD in the FROM and JOIN statements. Is there any way that I can do a global find and replace on all the views and procedures to replace the PROD with 1?

There has to be an easier way than to right click on each one, create the alter script to a new window, change the text, and press F5. Even if I can create all of the necessary alters and run them at once, that would work.

This could be done using the sys.objects table and the OBJECT_DEFINITION() function.

SELECT OBJECT_DEFINITION(id) AS ORIGINAL,
    REPLACE(OBJECT_DEFINITION(id),'VALUE_TO_BE_REPLACED', 'VALUE_TO_REPLACE_WITH') AS UPDATED
    ,*
FROM sys.sysobjects o
WHERE xtype = 'V'

The 'UPDATED' column from this query can be used to do a quick find and replace on whatever database or table name might be found in the view definition.  You will want to make sure you do the same for every possible version of the text you are searching.  For instance a database called 'db' might be written as [db] or db. Once you have what you want from the query just copy and paste to a query window and execute the whole thing.  Please make sure to backup the database prior to running it.  Also carefully proof read the output text so you can verify that the replace worked correctly and did not accidentally change more than you intended.

BTW, the answer WEI_DBA gave is perfectly acceptable also.  It's just a different approach to the same thing.  In his example, the GUI will aide in getting all of the object definitions to the query window.  Then you would just use Find/Replace(ctrl+h) to replace the database name.  To each his own in this case.
I don't know that there's an advantage in either approach except maybe that my approach will allow you to limit the query only to certain objects (views in this case) with additional filters added if needed in the where clause.   Generate scripts will allow you to filter to only views, stored procs, etc, and then individually select objects but additional filters will be limited to whatever the UI can do.


Migrating MySQL server to MariaDb or Percona? I have a MySQL application (MySQL 5.5) running on a virtual Ubuntu 16.04 (64bit) server with 2 kernels and 8 GB RAM. I consists of a few, small RW tables and appr. 30 read-only InnoDb tables with 100-150k rows in each (totaling ~ 1-2 GB data + indexes). Almost all queries gather data from several of the read-only tables (lots of different joins).

Wanting to increase overall performance, should I try to:

switch to MariaDb and Aria tables ?
switch to Percona and XtraDB ?
add more RAM (kernels) ?
or something else ?

The overall performance of MySQL/MariaDB/Percona and InnoDB/XtraDB etc, is minimal.  There are specific cases where one shines over another.  You should not expect a magical improvement.

Instead...  You should look at what is specifically causing trouble and tackle that.

High CPU:  Add composite index / revise query -- but first locate the slowly running query.

High I/O:  Same as high CPU, plus poor Joins, excessively sized datatypes (eg, BIGINT where SMALLINT UNSIGNED would suffice), etc.

Swapping:  This is terrible -- probably caused by carelessly increasing values in my.cnf

Come back with worst query, together with SHOW CREATE TABLE.

Adding RAM won't help -- your dataset is significantly smaller than RAM.  But do check innodb_buffer_pool_size.


How to optimize a query so that it seeks on one index first, and then another index after that I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two sets such that their times match to a threshold of 3hrs (or .125 days) and their distances to within 200 km of each other.

I have made indexes for both the mjd fields on both tables and spatial tables.

When I just join on the time constraint, the database calculates 100,000 matches in 8 seconds and computes the distances for all 100,000 matches in that time. The query looks like this:

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

And the executed plan is:

When sorted, 9 of the distances were under 200km, so there are matches. The trouble is, when I add the distance constraint and run this instead,

select top 10 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
and h.GeoPoint.STDistance(m.GeoPoint)<200000
option( table hint ( h, index(ix_MJD) ), table hint( m, index(ix_MJD) ) )

it goes away for a long time. Obviously, in 8 seconds, it could find 100,000 time matches, 9 of which were under 200km, so the optimizer must be trying something sub-optimal. The plan looks similar to above with a filter on the distances (I'm guessing).

I can force the use of the spatial index with this:

select top 5 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.GeoPoint.STDistance(m.GeoPoint)<200000
and h.mjd between m.mjd-.125 and m.mjd+.125
option( table hint ( h, index(ix_MJD), index(ix_GeoPoint) ), table hint( m, index(ix_MJD) ) )

which then takes 3 minutes to find 5 matches.

How do I tell the query optimizer to use the MJD index seek first, and then the spatial index second (or is that what it is doing already) and is there any way I can help it out by telling it how many matches to expect? If it can compute 100,000 matches with distances in 8 seconds that has 9 under 200km, shouldn't the addition of the spatial index make it faster not slower?

Thanks for any other tips or ideas.

EDIT: To answer the question what the plan looks like without the hints, this (and it takes forever):

It maybe also worth mentioning that there are almost 1M records in the one table and 8M in the other

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter.

But if you have a few million records within 200km, then it could be significantly worse.

You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the records in there that are close in time means checking each one.

Or else you're finding records by time, and you're getting results in time order. Then, filtering this list to the 200km radius is a matter of checking each one.

If you filter the data in two ranges like this, it becomes hard to apply the second filter using an index. You may be better off telling it not to use the spatial index if the time filter is the tighter one.

If both are large individually, and it's only together that they are tight, then you have a more complex issue, one that people have tried to solve for a long time, and which could be nicely resolved by indexes that cover 3D (and beyond) space. Except that SQL Server doesn't have them.

Sorry.

Edit: more info...

This is a similar problem to finding time ranges that cover a particular point in time. When you search for the records that start before that point, you then have an unordered mess of end times - and vice-versa. If you look for people in the phone book whose surnames start with F, you can't hope to find the people whose first names start with R very easily. And an index on first name doesn't help either for the same reason. Finding things in that next index is hard when your first index is not an equality.

Now, if you could change your date filter into an equality filter (or series of equality filters), then you could stand a chance, except that a spatial index is a special kind of index and can't be used as the second level in a composite index.

So you're left with an awkward situation, I'm afraid. :(

Edit: Try:

select top 100000 h.Time, m.Time, h.GeoPoint.STDistance(m.GeoPoint)/1000.0
from L2V5.dbo.header h join L2.dbo.MLS_Header m
on h.mjd between m.mjd-.125 and m.mjd+.125
where h.GeoPoint.STDistance(m.GeoPoint)/1000.0 < 200
option( table hint ( h, index(ix_MJD) ) );

Notice that I'm deliberately breaking the sargability by dividing by 1000 before comparing to 200. I want this work to be done in the Key Lookup.

Mind you, you could avoid the need for the lookups (and the hints) by INCLUDEing GeoPoint and Time in both ix_MJD indexes. That'll certainly take some of the heat out of the query plan.


List all tables from all user databases sys.tables lists all user tables from a single database, But it doesn't have their schema name.

If I know existing databases when creating the code, I can use USE to browse all databases, query sys.tables for its tables, and insert the list into a temptable to use later.

But, is there an easier way to do it, and with a unique query be able to retrieve all user tables from all user databases with their schema?

I have a slightly different approach:

I tend to caution against sp_msforeachdb because it is undocumented, unsupported, and has a known bug where it can silently skip databases, and Microsoft has no interest in fixing it (see here, here, here, here, and here).
I like to build a dynamic SQL statement that can be inspected before execution, or printed out to be massaged first, instead of just throwing it into a black box. I also like to make sure the statement only attempts to run against databases that are online and that the user can access.

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @sql = N'', @src = N' UNION ALL
SELECT ''$d'',
    s.name COLLATE SQL_Latin1_General_CP1_CI_AI,
    t.name COLLATE SQL_Latin1_General_CP1_CI_AI
  FROM [$d].sys.schemas AS s
  INNER JOIN [$d].sys.tables AS t
  ON s.[schema_id] = t.[schema_id]';

SELECT @sql = @sql + REPLACE(@src, '$d', name)
  FROM sys.databases
  WHERE database_id > 4
    AND [state] = 0
    AND HAS_DBACCESS(name) = 1;

SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));

PRINT @sql;
--EXEC sys.sp_executesql @sql;

Note: this will bomb on databases with a single quote/apostrophe (') in the name. Which seems fair, because who does that?

If you need to put the results into a temp table for later use:

CREATE TABLE #t(d SYSNAME, s SYSNAME, t SYSNAME);
INSERT #t EXEC sys.sp_executesql @sql;


Looking for a possible multi-column index I have a table highscore containing the columns:

game (text)
date (timestamp)
score (integer)
more irrelevant ones...

The query most often run on it is:

SELECT *
FROM highscore
WHERE game = :gamename
AND date BETWEEN :start AND :end
ORDER BY score DESC
LIMIT 10

I currently have three B-tree indexes on this table, one for each column above.

I'm thinking that I could further optimize performance by adding some sort of multi-column index, which starts with game. However, when thinking through the various options for the next column(s), I get stuck.

Can I use a multi-column index for further optimization here?

I'm using PostgreSQL 9.1

No wonder you get stuck. You are facing contradictory requirements, which a b-tree index cannot easily reconcile. You can hardly optimize the search for date and score at the same time. If you order by date, the scores are spread out arbitrarily - or vice versa.

There is a way though. You can make use of a set of partial indexes.
Consider the high-end solutions under this closely related question.