how to join below to queries in single query I have following query,

How can I join these two queries into one.

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
 -- SUM(I.IP_WT) as "TOTAL WEIGHT"
  CASE ORD_TYPE
    WHEN 'DOM-EXP'
    THEN 'Export to Chaina'
    WHEN 'DOM-SCS'
    THEN 'Export to USA'
    WHEN 'DOM-OEM'
    THEN 'Export to Oman'
    WHEN 'DOM-RET'
    THEN 'Export to Russia'
    WHEN 'EXPORT'
    THEN 'Export to Shri Lanka'
    ELSE 'N/A'
  END AS "ORDER TYPE" ,
  CASE PACK_TYPE
    WHEN 'EXP-STD'
    THEN 'Standard Export'
    WHEN 'DOM-STD'
    THEN 'Standard Domestic'
    ELSE 'NA'
  END AS "PACKING TYPE"
FROM HR.BAF1_PDI
GROUP BY ORD_NO, ORD_ITEM_NO,BATCH_NUMBER, ORD_TYPE, PACK_TYPE
ORDER BY 1,2,3;

and

select SUM(I.IP_WT) as "TOTAL WEIGHT" FROM HR.BAF1_PDI I GROUP BY I.ORD_NO;

Since you have different GROUPING SETS, you can use this clause:

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
  SUM(I.IP_WT) as "TOTAL WEIGHT"
  CASE ORD_TYPE
    WHEN 'DOM-EXP'
    THEN 'Export to Chaina'
    WHEN 'DOM-SCS'
    THEN 'Export to USA'
    WHEN 'DOM-OEM'
    THEN 'Export to Oman'
    WHEN 'DOM-RET'
    THEN 'Export to Russia'
    WHEN 'EXPORT'
    THEN 'Export to Shri Lanka'
    ELSE 'N/A'
  END AS "ORDER TYPE" ,
  CASE PACK_TYPE
    WHEN 'EXP-STD'
    THEN 'Standard Export'
    WHEN 'DOM-STD'
    THEN 'Standard Domestic'
    ELSE 'NA'
  END AS "PACKING TYPE"
FROM HR.BAF1_PDI
GROUP BY GROUPING SETS (
    (ORD_NO, ORD_ITEM_NO,BATCH_NUMBER, ORD_TYPE, PACK_TYPE),
    (I.ORD_NO)
)

From there you can filter out any unwanted rows. A more manual version is to create union compatible results from two union legs:

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
  NULL as "TOTAL WEIGHT"
  CASE ORD_TYPE
    WHEN 'DOM-EXP'
    THEN 'Export to Chaina'
    WHEN 'DOM-SCS'
    THEN 'Export to USA'
    WHEN 'DOM-OEM'
    THEN 'Export to Oman'
    WHEN 'DOM-RET'
    THEN 'Export to Russia'
    WHEN 'EXPORT'
    THEN 'Export to Shri Lanka'
    ELSE 'N/A'
  END AS "ORDER TYPE" ,
  CASE PACK_TYPE
    WHEN 'EXP-STD'
    THEN 'Standard Export'
    WHEN 'DOM-STD'
    THEN 'Standard Domestic'
    ELSE 'NA'
  END AS "PACKING TYPE"
FROM HR.BAF1_PDI
GROUP BY ORD_NO, ORD_ITEM_NO,BATCH_NUMBER, ORD_TYPE, PACK_TYPE

UNION ALL

select NULL   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  NULL         AS "ORDER ITEM NUMBER",
  NULL AS "BATCH COUNT",
  NULL          AS "ORDER WEIGHT",
  SUM(I.IP_WT) as "TOTAL WEIGHT",
  NULL AS "PACKING TYPE"
FROM HR.BAF1_PDI I GROUP BY I.ORD_NO;

A third alternaive is to use a sub-select:

SELECT BATCH_NUMBER   AS "BATCH NUMBER",
  ORD_NO              AS "ORDER NUMBER",
  ORD_ITEM_NO         AS "ORDER ITEM NUMBER",
  COUNT(BATCH_NUMBER) AS "BATCH COUNT",
  SUM(IP_WT)          AS "ORDER WEIGHT",
  ( SELECT SUM(IP_WT)
    FROM HR.BAF1_PDI
    WHERE ORD_NO = I.ORD_NO ) as "TOTAL WEIGHT"
  FROM ...

A 4:th alternative is to join in the second query

SUM(IP_WT)          AS "ORDER WEIGHT",
...
X.TOTAL WEIGHT,
...
FROM HR.BAF1_PDI
JOIN (
    select ORD_NO, SUM(IP_WT) as "TOTAL WEIGHT"
    FROM HR.BAF1_PDI
    GROUP BY ORD_NO
) X
    ON I.ORD_NO = X.ORD_NO
...


xml queries with exist and contains giving unexpected results I have a query as shown below.

DECLARE @tbl TABLE
(
 id INT,
 col XML
)
INSERT INTO @tbl
VALUES
(1,'<Root>
  <Row>
    <User>xyz</User>
    <Rowid>1</Rowid>
  </Row>
  <Maxrowid>1</Maxrowid>
</Root>'),
(2,'<Root>
  <Row>
    <User>xyz</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>mnj</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>'),(3,'<Root>
  <Row>
    <User>abs</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>xra</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>
')

--table before---
SELECT  *
FROM    @tbl t1
------------------

DECLARE @id varchar
SELECT @id = 'xyz'
SELECT  *
FROM    @tbl t1
WHERE col.exist('//*/text()[contains(.,sql:variable("@id"))]') = 1

I am expecting this query to output rows which has 'xyz' in xml column 'col' anywhere. But it returns all rows.(when i checked it returns all rows having 'x' in xml column. So please tell me why does it happen?where i am going wrong?Also please correct it.

Also i have another problem where below query for the same above xml does not return any output.I expect it to return xml column having 'xyz' anywhere in it.So please have a look at it also and correct my errors.

DECLARE @id varchar
SELECT @id = 'xyz'
SELECT  *
FROM    @tbl t1
WHERE t1.col.exist('//*[text()=sql:variable("@id")]')=1

You have not specified the length of @id. Using only varchar will in this case give you a length of one so the content of @id will be x.

Your first query checks for nodes that contain x and that matches all rows. The second query checks for nodes that has x as value and no row has that.

Change the declaration of @id to DECLARE @id varchar(3) and everything will work as expected.


How to debug "IO Error: End of TNS data channel" errors? I am seeing sporadic reports of an application failing certain queries with an "End of TNS data channel" error from the Oracle (10g) server.  Are there any queries I can run (e.g., with SQL*Plus or other tool) to ask the Oracle server what was the cause of such failures?  What logs should I look for?

You could check the alert log, as described in the Stack Overflow Q & A:

How to find and tail the Oracle alert log

...where the question author tardate says:

  I posted some bash and Perl scripts to find and tail the alert log on my blog some time back, and I'm surprised to see that post still getting lots of hits.
  The technique used is to lookup background_dump_dest from v$parameter.

The accepted answer by cagcowboy adds:

  If the background_dump_dest parameter isn't set, the alert.log will be put in $ORACLE_HOME/RDBMS/trace.


Having Non-Numeric values as measures in Fact table According to my research regarding having non-numeric value as measures, I found that having non-numeric won't be an optimal one.

So I planned to maintain numeric values. My scenario was
"Employee's Attendance" and the measure may be "Casual Leave", "Sick Leave", "UnInformed Leave", "Comp off".

Is that I need to maintain column for all of these (4 measures) and will be updating based on the corresponding user's status. If I maintain like that means if a new one added means it will be like structure of the cube getting changed.

Is that will be a good one or any optimal solution available?

As you noted in the comments for your other question your fact table data seems to be something like this:

+------------+
+ Sick leave +
+            +
+ Comp off   +
+ Sick leave +
+            +
+------------+

This kind of depends on your ETL loading procedures and reporting requirements.

You essentially have 2 options

Create separate measures

To achieve this you need to have to create 4 columns in  your fact table and have the corresponding value set to 1 or 0.

For example

+--------------------------------------------------------+
| Casual Leave | Sick Leave | UnInformed Leave |Comp off |
+--------------------------------------------------------+
| 1            | 0          | 0                |0        |
| 0            | 0          | 0                |0        |
| 0            | 1          | 0                |0        |
| 0            | 0          | 1                |0        |
| 0            | 0          | 0                |0        |
| 0            | 0          | 0                |1        |
+--------------------------------------------------------+

Then you can have a measure simply using a sum on all four columns to end up with 4 measures.

Create an extra dimension

You could create an extra dimension table for LeaveType like this:

+-----------------------------+
| Type_key | Type_Label       |
+-----------------------------+
| 0        | None             |
| 1        | Casual leave     |
| 2        | Sick leave       |
| 3        | Uninformed leave |
| 4        | Comp off         |
+-----------------------------+

And then in your ETL you can determine which type of leave it is.

+---------------------------+
| Leave_Type   | Leave_Days |
+---------------------------+
| 1            | 1          |
| 0            | 0          |
| 2            | 1          |
| 3            | 1          |
| 4            | 1          |
| 0            | 0          |
+---------------------------+

Then you can link that dimension to your fact table.

Decision points

The ease of implementation depends on your ETL process (do you have one?). One can be easier to implement over the other.

The behavior of the result is also a bit different. For example with multiple measures you have to add every measure to your report, with a dimension you have to put the dimension on an axis.

It also has an impact on possible ways to do MDX calculations, filtering and so on.

I cannot decide in your place what the best option is and would suggest you do a small proof of concept and play around with it to see what suits you best.

For example if you put your dimension on the horizontal axis in excel the leave types with no values will not be shown.
If you have several measures you will have an empty column the length of your report.

Another example is maintainability. Depending on your tooling and possible ETL setup, how much work is it to add a new leave type 'child sick'.


How should I save apache logs into a mysql table? This is a php script to view apache logs.

I want to save apache logs into a mysql database.

Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP 'some pattern';

a) Should I use one table for storing all the urls every time they are accessed even if they repeat and then do the REGEXP search and apply the tag to all of them?

b) Or it would be better to save one table with unique urls, and a second table with the id of the url and the time accessed? Then the tagging will be applied this table that has less rows if the url repeats itself.

If option 'b' is better, what kind of index should I use for unique urls? varchar(4000) primary key? I was thinking about creating a md5 hash of the string of the url and use that as primary key because it will be shorter.

I ask this question because I want to know what would be best performance when:

Tagging many urls with regexp search
Importing thousands of urls into one table and make sure they are unique

Thanks!

Because this is really logging data that you are capturing, I would first store it in its raw form, then ETL/normalize it as needed.

For searching, you can index the front part of the URLs first few characters. Let's say the options are http:// and https:// - then limit the index size to three characters passed the protocol part.

CREATE TABLE log (
datetime_created DATETIME,
url VARCHAR(1024),
domainname VARCHAR(255),
someotherdata VARCHAR(255),
...
INDEX `idx_domain` (domainname(3)))
ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

InnoDB will allow you to search the table w/o locking it.
Compression will help with disk space.
INDEX idx_domain (domainname(3)) will index the first 3 characters of the domain name column and speed up searches. However, the strategy for REGEXP will be to try to match on the front of the domain name.

Scale will eventually be an issue if the site becomes popular, so buyer beware.


Auditing all User activities Today I had to grant sysadmin rights for 2 users. ;(

As I couldn't change that, I at least want to see, what they are doing, so I am looking for a way, to audit all activities by these 2 Users.
Sadly I couldn't find anything on how to set this up, with the exception of using the Profiler (which I don't want to do).

Does anybody has an idea on how to setup the Audit function to audit all activities by these 2 users?

thank you very much in advance. :)

Today I had to grant sysadmin rights for 2 users.

Why?

There should be no Good Reason for anyone (except you and your Team) to have this level of access to any database.  If they're having problems that they cannot resolve for themselves, then they should be working with you to solve them, not working around you and leaving you with little or no chance of fixing anything that they might break.

Always keep the biggest and best tools in the toolbox for yourself.

Trying to Audit users with this level of privilege is pointless - whatever you do, they can undo and, if they do manage to bring the whole instance crashing down, there won't be much Audit logging worth looking at anyway.

Also, bear in mind that it is your job to keep the database running, not their's.  They are only interested in fixing their immediate problem, not necessarily in the overall performance and efficiency (or even continued operation) of the whole database.

At the very least, you should revoke this privilege as soon as practically possible.  if you can, do it tonight before you go home so that they have to request it again tomorrow.  Also, consider raising it to Management as a formal Risk to the Business Continuity Plan.


The answer to your question is that

your MySQL system has 1365 rows
the EXPLAIN for your MariaDB shows 224377 rows
you're now telling me that your running system has ~500,000
535,000 divided by 1365 = ~400
400 * 17 = 6800
6,800 / 60 = 113 minutes ~ 2 hours

First thing to try.

Put separate INDEXes on b and c and see how that works. If not, try below. Are they reasonably distinct - i.e. is their cardinality high? If they are UNIQUE, that would be great.

Second thing to try.

I suggest that you redesign your table A - you should split it up as I suggested earlier. Maybe you should ask a new question in this regard (pointing back to this thread)? Your query is at least O(n) - or maybe worse? Post the output of SHOW CREATE TABLE A\G and some sample data and we'll try and help you with your new query. Also, give the cardinalities of the important fields.

My suggestion is to keep a, d and e together. Create two new tables B and C and have a PRIMARY KEY in those point back to your new PRIMARY KEY to your new A. Use a new AUTO_INCREMENT as the PK for all three. See how that goes.


select rows where values equal the result of count(*) group by? I have a table like this:

Each keyword should have 4 pieces of data.

I want to select the keywords that have less than 4 pieces of data (less than 4 rows).

I used select count(*) from mytable group by keyword to achieve something like this:

how can I select the keywords based on the count(*) result?

Here I want the keyword b from the result grid.

You can either nest your select and use a where clause in the outer select:

select keyword
from (
    select keyword, count(*) as cnt
    from mytable
    group by keyword
) as t
where cnt < 4

or you can use having which essentially is a shorter form of the above:

select keyword
from mytable
group by keyword
having count(*) < 4


Converting .TPS (TopSpeed) to SQL I have an older application that uses TopSpeed as the database engine. I want to use some of the data contained in these tables in another application that uses SQL. To accomplish this, I purchased the TPS ODBC driver and used Access to move the data from the TPS tables to an SQL database by using the linked tables feature.

This works fine, but I'm looking for an automated solution (plus, the Access way is messy). Is there a tool out there that could help?

You don't have to use Access as a mediator.

You can look into SSIS or import utility to import data from .TPS to SQL. You have to select Flat file data source.

At the end of the import utility, you will have the option to save the package and then schedule it using SQL Agent job.

Refer to : How to Migrate Data From Top Speed Database Files

Also, look into SSMA (SQL Server Migration Assistant). I have used it for Sybase to SQL Migration, but not sure if it will be good fit for TopSpeed migration.


Database design for smartphone product and its features I want to create database for smartphone and its features with feature category

    Feature Category
------------------------
|id     name
------------------------
|1      Processor
|2      Memory
|3      Display
|_______________________

Features
------------------------------------------------
|id     name                feature_category_id
------------------------------------------------
|1      Process Speed           1
|2      Process Technology      1
|3      RAM                     2
|4      Screen Size             3
|_______________________________________________

Product
----------------------------------------
|id     name                Description
----------------------------------------
|1      Moto G4
|2      Iphone 6s plus
|3      Lenovo K4
|______________________________________

now i want to store features for particular product for e.g iphone 6s plus product have ram = 2GB, Screensize = 5 inch etc....
But i want best solution for database design.

Based on your original design; you need a many-to-many relationship added to the ones you already have:

CREATE TABLE features_x_products
(
    product_id integer NOT NULL REFERENCES product (id),
    feature_id integer NOT NULL REFERENCES feature (id),
    PRIMARY KEY (product_id, feature_id),

    value varchar(200) NOT NULL,
    unit varchar(50)
) ;

This creates an EAV model (Entity - Attribute - Value). It's got many caveats. The one is mostly dislike is the fact that you have to store all values as text (or varchar), unless you have various value columns (i.e.: value_number decimal ; value_text varchar(200) ; value_timestamp timestamp ; ...), but then you'll also need some extra meta-information on your attributes to find out wich column to retrieve.

References and alternatives:

Aaron Bertrand: What is so bad about EAV, anyway?
Alternative to EAV for dynamic fields in a star schema data warehouse
Using MySql 5.7 JSON columns for EAV

Side Note: The concept of best design doesn't really exist. There are good solutions to specific problems. Some solutions are good for many different problems, others only a apply to very few ones. Depending on how you're going to query the database, whether you access the database using from one programming language or another, might make using JSON specially appealing, or completely awful. Same applies for EAV.


B2B application: users has its own users B2B Scenario I am trying is that:
The following Roles exists in the system:

Super Admin
Super Admin Staff
Owner (client)
Admin (owner i.e. client and creates admin to define sales counters)
Sales Counters

That means there are users who'll create their own sub users e.g.
[FLAG-A] ROLE "SUPER ADMIN" creates a ROLE "Owner" and "OWNER" creates its own sub users ROLES i.e. "ADMN" & "SALES COUNTER".
Each role obviously has its info to login to the system
I tried to design database as following:

In this diagram I am assuming that main user creates sub user that in my view many to many relationship so I have to add table between them as "USER_PERSON".
Problem is to set "WHO CREATED USER" in the system. I mean which user has created sub user as described in [FLAG-A]

Please check this diagram

Your approach is unnecessarily complicated. Your situation boils down to this:

You have users (also persons), with their corresponding attributes, including login credentials and such, but also status and last login date.
A user (person) can have one (or more?) role(s).
A role has one or more permissions.
A user can be created by one other user, so the user's creator is among the user's attributes.

So, I'd model three entities:

User (with all the properties), with a self-reference to the user's creator.
Role, with a many-to-many relationship to User.
Permission, with a many-to-one (or many-to-many) relationship to Role.

That's it.


Passing array from one plpgsql function to another I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new table.

Parent function:

CREATE OR REPLACE FUNCTION foo(linktable REGCLASS,
                               inttable REGCLASS,
                               verttable REGCLASS)

DECLARE
    intersection_ids INT[];

-- ... SNIP

    BEGIN
        EXECUTE format('SELECT array_agg(id) from %s',inttable) INTO intersection_ids;
        EXECUTE format('
            SELECT tdgSetTurnInfo(%L,%L,%L,%L);
            ',  linktable,
                inttable,
                verttable,
                intersection_ids);
    END;

-- SNIP ...

Child function:

CREATE OR REPLACE FUNCTION bar(linktable REGCLASS,
                               inttable REGCLASS,
                               verttable REGCLASS,
                               intersection_ids INT[])

-- ... SNIP

    EXECUTE format('
        INSERT INTO %s (int_id,
                        ref_link_id,
                        match_link_id,
                        ref_azimuth,
                        match_azimuth)
        SELECT  int.id,
                l1.id,
                l2.id,
                degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %s int,
                %s v1,
                %s v2,
                %s l1,
                %s l2
        WHERE   int.id ANY %L
        AND     int.id = v1.intersection_id
        AND     int.id = v2.intersection_id
        AND     l1.target_node = v1.node_id
        AND     l2.source_node = v2.node_id
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        AND     l1.road_id != l2.road_id;
        ',  temptable,
            inttable,
            verttable,
            verttable,
            linktable,
            linktable,
            intersection_ids);

-- SNIP ...

When I run this code I get an error:

QUERY:

INSERT INTO tdggtitemptbl (int_id,
                ref_link_id,
                match_link_id,
                ref_azimuth,
                match_azimuth)
SELECT  int.id,
        l1.id,
        l2.id,
        degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
        degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
FROM    a_intersections int,
        a_net_vert v1,
        a_net_vert v2,
        a_net_link l1,
        a_net_link l2
WHERE   int.id ANY '{1,2,3,4,5,6,7,8,9,10,11,12,13}'
AND     int.id = v1.intersection_id
AND     int.id = v2.intersection_id
AND     l1.target_node = v1.node_id
AND     l2.source_node = v2.node_id
AND     l1.road_id IS NOT NULL
AND     l2.road_id IS NOT NULL
AND     l1.road_id != l2.road_id;

However, when I run similar code outside of the function and without the format() statement it runs without issues. Is there a problem with running my INT[] array through the format()? Or is it something else entirely?

You can avoid various complications by passing values as values with the USING clause:

CREATE OR REPLACE FUNCTION foo(linktable regclass,
                               inttable  regclass,
                               verttable regclass)
RETURNS void AS
$func$
BEGIN
   EXECUTE format(
      'SELECT tdgSetTurnInfo($1, $2, $3, array_agg(t.id)) FROM %s t'
     , linktable)
   USING  $1, $2, $3;  -- you could use parameter names as well
END
$func$ LANGUAGE plpgsql;

And pass the generated array directly in a single SELECT.

Note that $1, $2, $3 in EXECUTE reference the USING clause and only happen to be in sync with ordinal numbers of function parameters in USING  $1, $2, $3. Same notation, independent scope!

After fixing the immediate cause for the error (like @Craig commented):

WHERE   int.id ANY %L

WHERE   int.id = ANY (%L)

Simplify the 2nd function in a similar fashion:

CREATE OR REPLACE FUNCTION bar(linktable regclass,
                               inttable  regclass,
                               verttable regclass,
                               intersection_ids int[])

    -- ... SNIP

    EXECUTE format('
        INSERT INTO %1$s
               (int_id, ref_link_id, match_link_id, ref_azimuth, match_azimuth)
        SELECT  int.id, l1.id      , l2.id
              , degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom)))
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %2$s int
        JOIN    %3$s v1 ON v1.intersection_id = int.id
        JOIN    %3$s v2 ON v2.intersection_id = int.id
        JOIN    %4$s l1 ON l1.target_node = v1.node_id
        JOIN    %4$s l2 ON l2.source_node = v2.node_id
                     AND l2.road_id <> l1.road_id
        WHERE   int.id = ANY ($1)    -- fix syntax and pass as value!
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        ', temptable,
           inttable,
           verttable,
           linktable)
    USING intersection_ids;

    ...

The whole setup might be simplified further.


The client need 'SUPER' privileges to the reports DB in order to create and maintain my Functions I have problem with one of my client

Provided super privileges to the client James on  'Reports ' database , but the problem is the client need  'SUPER' privileges to the reports DB in order to create and maintain my Functions.  So I'm still unable to move forward...

BUT, I still need SUPER privileges to the reports DB in order to create and maintain my Functions.  So I'm still unable to move forward...                                 (This is what client responding , he can login but he can't create nor maintain functions on reports db server  )

After providing super privileges the client cant maintenance any of this functions nor he can create functions , how to work on this .

To modify functions and stored procedures, you need the ALTER ROUTINE privilege. If binary logging is enabled, you may also require the SUPER privilege.


Updating rows in a large table in sql server I want to update one large table that has 83,423,460 rows and growing.

The below query takes 8 minutes to execute successfully:

 UPDATE FPP_Invoice_Revenue
 SET    Till_Prev_Inv_Amt = Till_Prev_Inv_Amt_In_USD / 0.0285714286,
        Cur_Inv_Amt = Cur_Inv_Amt_In_USD / 0.0285714286,
        YTD_Inv_Amt = YTD_Inv_Amt_In_USD / 0.0285714286
 WHERE  SOW_Number = '20014378'

There exists one clustered index. I thought of disabling that index before updating and again will rebuild after update but that also did not work since rebuilding is taking a lot of time.

I have read somewhere this can be achieved by dividing into small parts,  but how can I divide the above query?

DDL:

CREATE TABLE [dbo].[FPP_Invoice_Revenue](
[Project_Code] [varchar](10) NOT NULL,
[Project_Desc] [varchar](50) NULL,
[SOW_Number] [varchar](10) NOT NULL,
[SOW_Desc] [varchar](50) NULL,
[Invoice_No] [varchar](50) NOT NULL,
[Inv_Month] [int] NOT NULL,
[Inv_Year] [int] NOT NULL,
[Billing_Date] [smalldatetime] NULL,
[Doc_Currency] [varchar](10) NULL,
[Vertical] [varchar](255) NULL,
[Till_Prev_Inv_Amt] [numeric](24, 10) NULL,
[Cur_Inv_Amt] [numeric](24, 10) NULL,
[YTD_Inv_Amt] [numeric](24, 10) NULL,
[Till_Prev_Inv_Amt_In_USD] [numeric](24, 10) NULL,
[Cur_Inv_Amt_In_USD] [numeric](24, 10) NULL,
[YTD_Inv_Amt_In_USD] [numeric](24, 10) NULL,
CONSTRAINT [PK_FPP_Invoice_Revenue] PRIMARY KEY CLUSTERED
(
[Project_Code] ASC,
[SOW_Number] ASC,
[Invoice_No] ASC,
[Inv_Month] ASC,
[Inv_Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

Execution Plan:

Adding an index on SOW_Number will allow SQL Server to very quickly identify the rows that need to be updated, without requiring a scan of the entire table (assuming a relatively small number of rows match the WHERE clause, making the index highly selective).

CREATE NONCLUSTERED INDEX IX_SOW_Number
ON dbo.FPP_Temp_Revenue(SOW_Number)
INCLUDE (
        Till_Prev_Inv_Amt_In_USD
        , Cur_Inv_Amt_In_USD
        , YTD_Inv_Amt_In_USD
    );

Even though you have SOW_Number defined as part of the clustered index, it is not the leading column of the index, which means SQL Server must scan the entire index looking for rows that fulfill the WHERE clause.  Adding the three columns listed in the INCLUDE clause will allow SQL Server to not perform a lookup into the clustered index for those values, which it would otherwise need to do to as they are used in the calculations inside the SET clause.

I wouldn't normally suggest adding an index like this without evaluating the workload carefully; however I'm assuming you don't have any indexes other than the clustered index you identify in your script.  Adding a single index in this case will most likely make a very big difference to the performance of your update, without dramatically affecting other portions of your workload.

I setup a test-bed on my laptop so I could test the performance of adding the suggested index vs having no index on SOW_Number.

First, I created a new database TestDB for this test (I'm using SQL Server on Linux RC3):

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON (NAME = 'TestDB_Primary', FILENAME = '/var/opt/mssql/data/TestDB_Primary.mdf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB)
LOG ON (NAME = 'TestDB_Log', FILENAME = '/var/opt/mssql/log/TestDB_Log.ldf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB);
GO

USE TestDB;
CREATE TABLE dbo.FPP_Invoice_Revenue(
         Project_Code varchar(10) NOT NULL
       , Project_Desc varchar(50) NULL
       , SOW_Number varchar(10) NOT NULL
       , SOW_Desc varchar(50) NULL
       , Invoice_No varchar(50) NOT NULL
       , Inv_Month int NOT NULL
       , Inv_Year int NOT NULL
       , Billing_Date smalldatetime NULL
       , Doc_Currency varchar(10) NULL
       , Vertical varchar(255) NULL
       , Till_Prev_Inv_Amt numeric(24, 10) NULL
       , Cur_Inv_Amt numeric(24, 10) NULL
       , YTD_Inv_Amt numeric(24, 10) NULL
       , Till_Prev_Inv_Amt_In_USD numeric(24, 10) NULL
       , Cur_Inv_Amt_In_USD numeric(24, 10) NULL
       , YTD_Inv_Amt_In_USD numeric(24, 10) NULL
CONSTRAINT PK_FPP_Invoice_Revenue PRIMARY KEY CLUSTERED
(
    Project_Code ASC
    , SOW_Number ASC
    , Invoice_No ASC
    , Inv_Month ASC
    , Inv_Year ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY];
GO

Next, I inserted 10,000,000 rows to have a sufficiently large table.  I know, this is a bit smaller than your table, but it provides enough data to make reasonable extropolations:

TRUNCATE TABLE dbo.FPP_Invoice_Revenue;
;WITH v AS (
       SELECT v.num
       FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.FPP_Invoice_Revenue
SELECT Project_Code = CONVERT(varchar(10), ROW_NUMBER() OVER (ORDER BY v1.num, v2.num))
       , Project_Desc = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , SOW_Number = v5.num /* only 10 distinct values */
       , SOW_Desc = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , Invoice_No = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , Inv_Month = ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 12
       , Inv_Year = 2009 + ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 8
       , Billing_Date = DATEFROMPARTS(2009 + ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 8, 1, (ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 12) + 1)
       , Doc_Currency = CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
       , Vertical = CONVERT(varchar(255), CRYPT_GEN_RANDOM(255))
       , Till_Prev_Inv_Amt = CONVERT(numeric(24, 10), 1)
       , Cur_Inv_Amt = CONVERT(numeric(24, 10), 2)
       , YTD_Inv_Amt = CONVERT(numeric(24, 10), 3)
       , Till_Prev_Inv_Amt_USD = CONVERT(numeric(24, 10), 4)
       , Cur_Inv_Amt_In_USD = CONVERT(numeric(24, 10), 5)
       , YTD_Inv_Amt_In_USD = CONVERT(numeric(24, 10), 6)
FROM v v1 --10 rows
       CROSS JOIN v v2 --100 rows
       CROSS JOIN v v3 --1000 rows
       CROSS JOIN v v4 --10000 rows
       CROSS JOIN v v5 --100000 rows
       CROSS JOIN v v6 --1000000 rows
       CROSS JOIN v v7;--10000000 rows

Next, I create the non-clustered index I've suggested above:

CREATE NONCLUSTERED INDEX IX_SOW_Number
ON dbo.FPP_Invoice_Revenue(SOW_Number)
INCLUDE (Till_Prev_Inv_Amt, Cur_Inv_Amt, YTD_Inv_Amt);

This took about 1:20 to create on my laptop.

Now, the query:

UPDATE dbo.FPP_Invoice_Revenue
SET Till_Prev_Inv_Amt_In_USD = Till_Prev_Inv_Amt / 0.0285
    , Cur_Inv_Amt_In_USD = Cur_Inv_Amt / 0.0285
    , YTD_Inv_Amt_In_USD = YTD_Inv_Amt / 0.0285
WHERE SOW_Number = '6';

The test data is evenly distributed on 10 SOW_Number values; WHERE SOW_Number = '6' indicates we'll update 1,000,000 rows:

  (1000000 row(s) affected)

The actual execution plan for the update query:

The execution statistics for this:

  SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.
  Table 'FPP_Invoice_Revenue'. Scan count 1, logical reads 6567261, physical reads 8920, read-ahead reads 209603, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1000000 row(s) affected)

  SQL Server Execution Times:
    CPU time = 9376 ms,  elapsed time = 13689 ms.

As you can see from the above, the query only took around 14 seconds to update 1,000,000 rows.  If SOW_Number is more selective, the time required will be proportionally less.

As you've identified in your question, dropping the clustered index does not help increase performance of the update query.  Understand that all data in a table that has a clustered index is in fact stored in the clustered index.  Dropping and re-creating the clustered index will result in all 83 million rows being written to the log while data is moved in and out of the clustered index.  This is a lot of extra I/O that is not necessary for the update to succeed.

You have SOW_Number defined as a varchar(10); if the content of that column is always actually 10 characters long you might consider modifying it to be a char(10) instead of varchar(10), since that is actually more efficient.  Consider this for all the varchar columns you have.  Also, does Vertical actually need to be 255 characters long?  Presumably, Inv_Month and Inv_Year are actually not required to store up to 2,147,483,647?  You could likely convert those columns to smallint and save 4 bytes per row.  Keeping in mind the columns in the clustered index key are used (duplicated) in every non-clustered index, the space savings from right-sizing the data might be substantial.


Automatically detect table name in MSSQL Server database using stored function I'm working on implementing Change Data Tracking in a SQL Server database and I set up a single table to test changes.  In the past, changes have been made manually, but I'd like to create a way to track updates and deletions automatically through the built-in CDC process. In many cases, these changes are spread across dozens of different tables, so the ideal situation would be to create a view that collocates all of the changed data.

CDC works at the table level and doesn't track the user who makes changes. I found a way to add user data to the CDC tracking tables by creating a new user_name column and adding SUSER_SNAME() to the "Default Value or Binding" column property using the SQL command: "ALTER TABLE cdc.dbo_MyTable_CT ADD UserName nvarchar(50) NULL DEFAULT(SUSER_SNAME())" from a separate Stack Overflow question - (https://stackoverflow.com/questions/869300/sql-server-2008-change-data-capture-who-made-the-change).

I'm trying to find a similar stored function to track the table name from this list - https://msdn.microsoft.com/en-us/library/ms187812.aspx.

So far the only stored function I've successfully implemented from the list is db_name() which automatically returns the database name.  I've tried schema_name and object_name, but those automatically revert to N'schema_name()' or N'object_name()' in the design view.  In the table view, the changed rows return (schema_name()) or (object_name()).

Does anyone know how to automatically populate the a column with the table name of the table from which changes were made using some sort of stored function?

I think you would need to use a trigger on the table(s) in question in order to get the table name.

How I have implemented table change tracking is by just that: 1) Create a table to hold the changes and 2) Create a trigger on each table that needed to be tracked.

The triggers ALL look like this (where you replace 'XX_YOURTABLE_XX' with the table in question and [tr_XXXX] with a unique trigger name ):

CREATE TRIGGER [dbo].[tr_XXXX] on [dbo].[XX_YOURTABLE_XX] for INSERT, UPDATE, DELETE
AS

DECLARE
    @bit INT ,
    @field INT ,
    @maxfield INT ,
    @char INT ,
    @fieldname VARCHAR(128) ,
    @TableName VARCHAR(128) ,
    @PKCols VARCHAR(1000) ,
    @sql VARCHAR(2000),
    @UserName VARCHAR(128) ,
    @Type char(1) ,
    @PKSELECT VARCHAR(1000)

    SELECT @TableName = 'XX_YOURTABLE_XX'

    -- Get User
    IF object_id('tempdb..#TmpUser') IS NOT NULL
        SELECT  @UserName = TheUser FROM #TmpUser
    ELSE
        SELECT  @UserName = system_user

    -- Action
    IF EXISTS (SELECT * FROM INSERTED)
        IF EXISTS (SELECT * FROM DELETED)
            SELECT @Type = 'U'  --UPDATE
        ELSE
            SELECT @Type = 'I'  --INSERT
    ELSE
        SELECT @Type = 'D'      --DELETE

    -- get lISt of columns
    SELECT * INTo #ins FROM INSERTED
    SELECT * INTo #del FROM DELETED

    -- Get primary key columns for full outer join
    SELECT  @PKCols = coalesce(@PKCols + ' AND', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE   pk.TABLE_NAME = @TableName
            AND CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- Get primary key SELECT for INSERT
    SELECT @PKSELECT = coalesce(@PKSELECT+'+','') + '''<' + COLUMN_NAME + '=''+convert(VARCHAR(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
    FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE   pk.TABLE_NAME = @TableName
            AND CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    IF @PKCols IS NULL
    BEGIN
        RAISERROR('no PK on table %s', 16, -1, @TableName)
        RETURN
    END

    SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
    WHILE @field < @maxfield
    BEGIN
        SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
        SELECT @bit = (@field - 1 )% 8 + 1
        SELECT @bit = power(2,@bit - 1)
        SELECT @char = ((@field - 1) / 8) + 1
        IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
        BEGIN
            SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @field
            SELECT @sql =       'INSERT tbl_TRACKING (Type, TableName, PK, FieldName, OldValue, NewValue, UserName)'
            SELECT @sql = @sql +    ' SELECT ''' + @Type + ''''
            SELECT @sql = @sql +    ',''' + @TableName + ''''
            SELECT @sql = @sql +    ',' + REPLACE(REPLACE(REPLACE(@PKSELECT,'<',''),'>',''),'PriKeyVal=','')
            SELECT @sql = @sql +    ',''' + @fieldname + ''''
            SELECT @sql = @sql +    ',convert(VARCHAR(1000),d.' + @fieldname + ')'
            SELECT @sql = @sql +    ',convert(VARCHAR(1000),i.' + @fieldname + ')'
            SELECT @sql = @sql +    ',''' + @UserName + ''''
            SELECT @sql = @sql +    ' FROM #ins i full outer join #del d'
            SELECT @sql = @sql +    @PKCols
            SELECT @sql = @sql +    ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
            SELECT @sql = @sql +    ' or (i.' + @fieldname + ' IS NULL AND  d.' + @fieldname + ' IS not NULL)'
            SELECT @sql = @sql +    ' or (i.' + @fieldname + ' IS not NULL AND  d.' + @fieldname + ' IS NULL)'
            exec (@sql)
        END
    END

GO

The tracking table looks like this:

CREATE TABLE [dbo].[tbl_TRACKING](
    [Type] [char](1) NULL,
    [TableName] [varchar](128) NULL,
    [PK] [varchar](1000) NULL,
    [FieldName] [varchar](128) NULL,
    [OldValue] [varchar](1000) NULL,
    [NewValue] [varchar](1000) NULL,
    [ActionDate] [datetimeoffset](3) NULL,
    [UserName] [varchar](128) NULL,
    [AppName] [varchar](128) NULL,
    [ComputerName] [varchar](128) NULL
)

Hope that helps


Insert into a DB that was selected via other DB? I have a table that stores what database should I use for a given device( specified by its IMEI), like this:

imeiDB(
IMEI VARCHAR(15),
db VARCHAR(100));

Let's say I have the database device1(IMEI:123456789123456) is using stored in imeiDB:

123456789123456 | device1db

Supposing I have not selected the database to store the data, how can I select it on the insert into query of my table device1db.table1?

table1(IMEI VARCHAR(15),data VARCHAR(10))

I tried this but it did not work:

INSERT INTO (SELECT db FROM imeiDB where IMEI=123456789123456).table1 (IMEI,data) VALUES (123456789123456,"somestuff")

Any suggestions?

What you need is some Dynamic SQL.

STEP 01 : Retrieve the DB

SET @IMEI = 123456789123456;
SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI;

STEP 02 : Create the SQL to do the INSERT into the retrieve DB

SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"');
SELECT @sql\G

STEP 03 : Execute the SQL Statement you just created

PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

EPILOGUE

Execute them in that order

SET @IMEI = 123456789123456;
SELECT db INTO @db FROM imeiDB WHERE IMEI = @IMEI;
SET @sql = CONCAT('INSERT INTO ',@db,'.table1 (IMEI,data) VALUES (',@IMEI,',"somestuff"');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

GIVE IT A TRY !!!


Oracle Patching & Database Upgrade - Which Order I have an Oracle instance running 11.2.0.2.0:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I am going to upgrade to 11.2.0.3.11.

My question is which order should I perform the upgrade?

Option 1

Install 11.2.0.3
Upgrade Database
Patch to 11.2.0.3.11
Patch Database

Option 2

Install 11.2.0.3
Patch to 11.2.0.3.11
Upgrade Database

3<4, I opt for option 2 approach.

This is also what Oracle instructs us to do in for example this document:

http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD12394

  After you have installed the software for Oracle Database 11g Release
  2 (11.2) and any required patches, Oracle recommends that you analyze
  your database before upgrading it to the new release


Bulk Insert into view and find missing values I have 2 tables, Categories, and SubCategories:

CREATE TABLE [dbo].[tbCategories]
(
  [CategoryID] [int] IDENTITY(1,1) NOT NULL,
  [CategoryName] [varchar](250) NULL,
)

CREATE TABLE [dbo].[tbSubCategories]
(
  [SubCategoryID] [int] IDENTITY(1,1) NOT NULL,
  [CategoryID] [int] NULL,
  [SubCategory] [varchar](250) NULL
)

I want to Import Sub-Categories with a csv file, which contains the CategoryName and the SubCategory.

Here's an example:

So I get the CategoryName and the Sub-Category Name.  But I need the CategoryID. The user won't know what that is, they will only know the Category Name. What would be this best way to do this?

I already have a bulk insert method (using c#) that works fine if I import a csv file with CategoryID and SubCategoryName.  I thought I can insert into a view to achieve this. Something like:

CREATE VIEW [dbo].[vSubCategories]
AS

SELECT c.CategoryName, s.SubCategory
FROM dbo.tbCategories c
INNER JOIN dbo.tbSubCategories s
ON c.CategoryID = s.CategoryID

Then I can use a trigger with 'INSTEAD OF INSERT'.  But how do I get SQL to automatically pick up the CategoryID which should be inserted into SubCategory table?

In short, I want to import the csv file above, and see something similar in the tbSubCategories table as below:

For the purpose of this answer, I am assuming your INSERT statement looks something like this:

INSERT INTO
  dbo.vSubCategories (CategoryName, SubCategory)
SELECT
  CategoryName,
  SubCategory
FROM
  ...  /* reading rows either from the CSV directly or
          from a staging table into which the CSV has been imported */
;

So, the first thing your trigger needs to do is to insert the (new) categories into tbCategories:

INSERT INTO
  dbo.tbCategories (CategoryName)
SELECT
  CategoryName
FROM
  inserted
EXCEPT  -- do not insert already existing category names
SELECT
  CategoryName
FROM
  dbo.tbCategories
;

Now all the category names in the imported dataset can be mapped to their IDs using the tbCategories table. And that is what the trigger should do when inserting the subcategories:

INSERT INTO
  dbo.tbSubCategories (CategoryID, SubCategory)
SELECT
  c.CategoryID,
  n.SubCategory
FROM
  inserted AS n
  INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
EXCEPT  -- exclude existing sub-categories, just in case
SELECT
  CategoryID,
  SubCategory
FROM
  dbo.tbSubCategories
;

For reference, here is a complete trigger script:

CREATE TRIGGER dbo.vSubCategories_InsteadOfInsert
ON dbo.vSubCategories
INSTEAD OF INSERT
AS
BEGIN
  -- insert categories
  INSERT INTO
    dbo.tbCategories (CategoryName)
  SELECT
    CategoryName
  FROM
    inserted
  EXCEPT  -- do not insert already existing category names
  SELECT
    CategoryName
  FROM
    dbo.tbCategories
  ;
  -- insert sub-categories
  INSERT INTO
    dbo.tbSubCategories (CategoryID, SubCategory)
   SELECT
    c.CategoryID,
    n.SubCategory
  FROM
    inserted AS n
    INNER JOIN dbo.tbCategories AS c ON n.CategoryName = c.CategoryName
  EXCEPT  -- exclude existing sub-categories, just in case
  SELECT
    CategoryID,
    SubCategory
  FROM
    dbo.tbSubCategories
  ;
END;


Complex joins in multiple subqueries Sorry to keep bugging you guys, but SQL is still new to me. I have these tables:
Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency.
The goal is to be able to run one query with multiple subqueries (a WHERE clause is required) that will return only products that are sold in ALL store locations, and nothing else. It also has to be scalable so that if any stores open or close, the code does not need to be altered. I have these ramblings to get me started, but I do not know where to go from there:
The first select statement below is what needs to be displayed when the query succeeds.
    SELECT Store_location.store_name,Product.product_name,Sizes.size_option,
    COUNT(store_location.store_id) AS store_count

JOIN Sells ON Sells.store_location_id = Store_location.store_location_id
JOIN Product ON Product.product_id = Sells.product_id

JOIN ON Available_in.product_id = Product.product_id
JOIN ON Available_in.sizes_id = Sizes.sizes_id

I tried to get the joins done to show where the tables I need to use have foreign key constraints. IF you need any additional information, I can provide it. I added a link showing the contents of all the involved tables. I know I need to embed at least one subquery in a WHERE statement, but not sure what to put in there. I know it's a lot of information to go over, and I understand if nobody has the time to help, but any guidance at all would be appreciated.

I realize it's a late request, but if anyone can help me with a way to also use EXISTS on this I'd be very appreciative.

Seems like the most difficult part if finding out which products are sold in all stores. As with most things, there's more than one way to do this, but the following query should return all product IDs that are sold in all stores:

SELECT product_id
  FROM Product
EXCEPT -- exclude product IDs that are not sold in all stores
SELECT product_id
  FROM (-- Generate list of product IDs and the stores that do NOT sell them
        -- select all possible products and stores ...
        SELECT product_id, store_id
          FROM Product CROSS JOIN Store_location
        EXCEPT -- ... for the products actually sold by each store
        SELECT product_id, store_id
          FROM Sells
       ) Not_At_All_Stores

The sub-query takes the list of all possible products and stores, and removes the combos that represent products actually sold in the stores in question. This just leaves products that are not sold at the stores indicated.

Then, we take the full list of products, and remove any product that we (now) know are not sold in at least one store. That just leaves the products sold at all stores.

From here, it's just a matter of joining everything together. You'll wind up with a FROM clause like this:

SELECT Store_location.store_name
      ,Product.product_name
      ,Sizes.size_option
  FROM (
        SELECT product_id
          FROM Product
        EXCEPT -- exclude product IDs that are not sold in all stores
        SELECT product_id
          FROM (-- Generate list of product IDs and the stores that do NOT sell them
                -- select all possible products and stores ...
                SELECT product_id, store_id
                  FROM Product CROSS JOIN Store_location
                EXCEPT -- ... for the products actually sold by each store
                SELECT product_id, store_id
                  FROM Sells
               ) Not_At_All_Stores
       ) uprod -- for a Universally sold PRODuct
         INNER JOIN Product ON (uprod.product_id = Product.product_id)
         INNER JOIN Available_in ON (uprod.product_id = Available_in.product_id
           INNER JOIN Sizes ON (Available_in.size_id = Sizes.size_id)
         INNER JOIN Sells ON (uprod.product_id = Sells.product_id)
           INNER JOIN Store_location ON (Sells.store_id = Store_location.store_id)
 ORDER BY store_name, product_name, size_option

No WHERE clause is necessary (based on what you asked for). Note that the store information is redundant - since these products are sold at all stores, you're just getting 5 copies of the same underlying result, with one of the 5 store names prepended to each copy. The store name is much more interesting for products that aren't sold at every store.

NOTE: Depending on how your Product table is indexed, you could add the product_name column into the Not_At_All_Stores subquery, and carry it through from there. However, this does mean that we'd have to include it in our list of all possible products and stores, which would consume more memory while doing our EXCEPT operations.

UPDATE: From your latest comments, apparently this assignment requires that you use a WHERE clause, a subquery in the WHERE clause, and EXISTS.  To get there from here:

Drop uprod from the query; anything linked to uprod.product_id should link to Product.product_id instead;
Use the very first query in may answer as your sub-query;
You want the records where the product_id exists in the subquery.

Since this is a homework assignment, I'll let you actually assemble it, at least.

use the very first query in my answer as your sub_query, and look for product


Test:

SELECT CASE WHEN sender_id = 1 THEN receiver_id ELSE sender_id END user_id
FROM messages
WHERE 1 IN (sender_id, receiver_id)
GROUP BY user_id
ORDER BY MAX(id) DESC LIMIT 3


How do I load this SAS text file to SQL Server 2005 Every 3 months, I have to load several fixed-width text files into our SQL Server 2005 database.  They can have anywhere from 5 to 798 columns (seriously) and the columns change pretty regularly.

Here's an example of the fixed-width data file:

010001    2000040620000406001200004060220000406
010001    2000040620001116002        0020000406
010006    2007051620070516001200706220120070516
010006    2010071520100908002        0020100715
010006    2010071520100715001201007300120100715
010007    1993110919931109001199311230219931109

With the text files, I also receive an "SAS" file that documents the columns and data types.  Here's an example:

/************************************************************************
*           Job Number: 119164
*     Procedure Number: 6
*        Record Length: 47
*************************************************************************/

INPUT

         @1   PRVDR_NUM                         $CHAR10.
        @11   SRVY_DT                           $CHAR8. /*YYYYMMDD*/
        @19   CYC_VISIT_DT                      $CHAR8.
        @27   CYC_VISIT_NUM                     3.
        @30   POC_SGN_DT                        $CHAR8.
        @38   LSC_BLDG_CNT                      2.
        @40   SRVY_CMPLTN_DT                    $CHAR8.
;

***************************************************************;
***   SAS LABEL Statement Follows                              ;
***************************************************************;

LABEL

      PRVDR_NUM                         = 'CCN'
      SRVY_DT                           = 'Survey Date'
      CYC_VISIT_DT                      = 'Cycle Visit Date'
      CYC_VISIT_NUM                     = 'Cycle Visit Number'
      POC_SGN_DT                        = 'Administrator Signed POC Date'
      LSC_BLDG_CNT                      = 'Building Count'
      SRVY_CMPLTN_DT                    = 'Survey Completion Date'
;

The person I get this file from calls it "SAS Input Code"

Is there a solution for loading the fixed-width file that can also use the SAS file to create the table?  My current solution is too elaborate, and I really hope there's an alternative.

I would write a python script that would,

parse the columns descriptor file, get their widths and names
iterate over all the data in the input file, splitting out the data into variables
construct a sql query with these variables to the sql server with SQLAlchemy to be sent one by one or as a batch of rows or 1000 at a time

This script would take the columns descriptor file and the data file. The script would also have a database config file containing the database connection string or you can pass that as a third parameter.

I would write this for you, but I don't have time right now. This is a neat problem to solve. Python is perfect for this however. I wish my SAS problem was so easy.


How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration? I have a function with 5 parameters,

arguments p1, and p2 are mandatory
arguments p3, p4, and p5 are optional and have a DEFAULT set

The function looks like this,

CREATE FUNCTION test_func(
  p1 int,
  p2 int,
  p3 int DEFAULT 3,
  p4 int DEFAULT 4,
  p5 int DEFAULT 5
)
  RETURNS int AS
$$
  SELECT p5;
$$ LANGUAGE sql;

I can invoke it like this,

SELECT test_func(1,2,3,4,5);
 test_func
-----------
         5

But I would like to allow p3 and p4 to default and invoke it like this,

SELECT test_func(1,2,5);

Is there a way to specify the value for the last optional parameter (p5) without giving values to the preceding ones (p3, p4)? I don't want to hard-code default values for the p3, p4 in the call for test_func, because I may need to change their default values in the future.

You can simply supply the fifth argument with Mixed Notation using the => to separate the parameter name from the argument,

SELECT test_func(1,2,3,p5=>5);
test_func
-----------
         5
(1 row)

From the docs on mixed notation with the one caveat (from the docs),

  Named and mixed call notations currently cannot be used when calling an aggregate function (but they do work when an aggregate function is used as a window function).


Run PS-Script after job is done on 3 instances - SQL Server I have backup jobs running on a server with 3 instances.

Link to script: https://ola.hallengren.com/sql-server-backup.html

The solution I found for the other 2 servers with only once instance was to create a job which has the backup scripts as steps via TSQL and then simply runs the PowerShell script.

Is there a way to do this on a multi-instance server?

Server is running SQL Server 2012

You have a few options on how you could handle this...

Create a Scheduled Task on that server that contains a PowerShell script. The first part of that script contains a workflow that would iterate over each instance and call the backup job on each instance. Use a foreach -parallel would allow all the jobs to start at one time. You would then need to script out a while loop to wait and see that all jobs have completed. Then the end of that PowerShell script just call the code you need to run.
Pick an instance and use it as a "central" location to actually run the backups against each instance. You could utilize linked servers to remotely call Ola' scripts. This would offer a bit more control and ease of management because you would know once the command finished the backup for that instance are complete. Your last step would just be your PowerShell script.
Leave each backup job on the given instance. Add a scheduled task that calls your PowerShell script, but at the start of that script check to make sure a recent backup has been done. If not, just stop the script. Schedule that task to run between an the average end time of the backup jobs until a given a few hours later. This would ensure at some point it will find that the backups on all instances are current, then execute your code.


How to store time series data I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values.

An example would be modeling a car and tracking its various attributes during a trip. For example:

timestamp | speed | distance traveled | temperature | etc

What would be the best way to store this data so that a web application can efficiently query the fields to find max, mins, and plot each data set over time?

I started a naive approach of parsing the data dump and caching the results so that they would never have to be stored. After playing with it a bit, however, it appears this solution would not scale long term due to memory constraints and if the cache were to be cleared, then all data would need to be re-parsed and re-cached.

Also, assuming that data is tracked every second with the rare possibility of 10+ hour data sets, is it generally advised to truncate the data set by sampling every N seconds?

There's really no one 'best way' to store time series data, and it honestly depends on a number of factors.  However, I'm going to focus on two factors primarily, with them being:

(1) How serious is this project that it deserves your effort to optimize the schema?

(2) What are your query access patterns really going to be like?

With those questions in mind, let's discuss a few schema options.

Flat Table

The option to use a flat table has a lot more to do with question (1), where if this isn't a serious or large-scale project, you'll find it much easier not to think too much about the schema, and just use a flat table, as:

CREATE flat_table(
  trip_id integer,
  tstamp timestamptz,
  speed float,
  distance float,
  temperature float,
  ,...);

There aren't many cases where I'd recommend this course, only if this is a tiny project which doesn't warrant much of your time.

Dimensions and Facts

So, if you've cleared the hurdle of question (1), and you want a more performance schema, this is one of the first options to consider.  It includes some basic normailization, but extracting the 'dimensional' quantities from the measured 'fact' quantities.

Essentially, you'll want a table to record info about the trips,

CREATE trips(
  trip_id integer,
  other_info text);

and a table to record timestamps,

CREATE tstamps(
  tstamp_id integer,
  tstamp timestamptz);

and finally all of your measured facts, with foreign key references to the dimension tables (that is meas_facts(trip_id) references trips(trip_id) & meas_facts(tstamp_id) references tstamps(tstamp_id))

CREATE meas_facts(
  trip_id integer,
  tstamp_id integer,
  speed float,
  distance float,
  temperature float,
  ,...);

This may not seem like it is all that helpful at first, but if you have for example thousands of concurrent trips, then they may all be taking measurements once per second, on the second.  In that case, you'd have to re-record the time stamp each time for each trip, rather than just using a single entry in the tstamps table.

Use case: This case will be good if there are many concurrent trips for which you are recording data, and you don't mind accessing all of the measurement types all together.

Since Postgres reads by rows, any time you wanted, for example, the speed measurements over a given time range, you must read the whole row from the meas_facts table, which will definitely slow down a query, though if the data set you are working with is not too large, then you wouldn't even notice the difference.

Splitting Up Your Measured Facts

To extend the last section just a bit further, you could break apart your measurements into separate tables, where for example I'll show the tables for speed and distance:

CREATE speed_facts(
  trip_id integer,
  tstamp_id integer,
  speed float);

and

CREATE distance_facts(
  trip_id integer,
  tstamp_id integer,
  distance float);

Of course, you can see how this might be extended to the other measurements.

Use case: So this won't give you a tremendous speed up for a query, perhaps only a linear increase in speed when you are querying about one measurement type.  This is because when you want to look up info about speed, you need only to read rows from the speed_facts table, rather than all the extra, unneeded info that would be present in a row of the meas_facts table.

So, you need to read huge bulks of data about one measurement type only, you could get some benefit.  With your proposed case of 10 hours of data at one second intervals, you'd only be reading 36,000 rows, so you'd never really find a significant benefit from doing this. However, if you were to be looking at speed measurement data for 5,000 trips that were all around 10 hours, now you're looking at reading 180 million rows.  A linear increase in speed for such a query could yield some benefit, so long as you only need to access one or two of the measurement types at a time.

Arrays/HStore/ & TOAST

You probably don't need to worry about this part, but I know of cases where it does matter.  If you need  to access HUGE amounts of time series data, and you know you need to access all of it in one huge block, you can use a structure which will make use of the TOAST Tables, which essentially stores your data in larger, compressed segments.  This leads to quicker access to the data, as long as your goal is to access all of the data.

One example implementation could be

CREATE uber_table(
  trip_id integer,
  tstart timestamptz,
  speed float[],
  distance float[],
  temperature float[],
  ,...);

In this table, tstart would store the time stamp for the first entry in the array, and each subsequent entry would be the value of a reading for the next second.  This requires you to manage the relevant time stamp for each array value in a piece of application software.

Another possibility is

CREATE uber_table(
  trip_id integer,
  speed hstore,
  distance hstore,
  temperature hstore,
  ,...);

where you add your measurement values as (key,value) pairs of (timestamp, measurement).

Use case: This is an implementation probably better left to someone who is more comfortable with PostgreSQL, and only if you are sure about your access patterns needing to be bulk access patterns.

Conclusions?

Wow, this got much longer than I expected, sorry.  :)

Essentially, there are a number of options, but you'll probably get the biggest bang for your buck by using the second or third, as they fit the more general case.

P.S.: Your initial question implied that you will be bulk loading your data after it has all been collected.  If you are streaming the data in to your PostgreSQL instance, you will need to do some further work to handle both your data ingestion and query workload, but we'll leave that for another time. ;)


Unable to retrieve query plan of running query because of blocking I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes.  Probably some plan caching issue.  But the problem is that I am unable to retrieve the query plan because of blocking in tempdb while the query is running.  I am using the below dmv's to try to retrieve the query plan

FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) s3
where object_name ( s2.objectid, s2.dbid) = 'sp_name'
ORDER BY statement_start_offset

The above query to retrieve the plan gets blocked by the long running  problem query because the above query needs a schema S lock on an object on tempdb.  They long running query queries and updates a temp table and has option recompile on it.

Has anyone come across this issue?

Try flipping this predicate from

where object_name ( s2.objectid, s2.dbid) = 'sp_name'

to something like

where object_id = object_id('sp_name')

You might get a simpler plan that aviods whatever resource is blocked in TempDb.


Decode Base64 String Natively in SQL Server I have a varchar column in a table in SQL Server which hold a base64-encoded text string which I would like to decode into it's plain text equivalent

Does SQL Server have any native functionality to handle this type of thing?

A sample base64 string:

cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==

Which decodes to:

role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

Figured it out:

SELECT
    CONVERT
    (
        VARCHAR(MAX),
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS RESULT
FROM
    (
        SELECT 'cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==' AS BASE64_COLUMN
    ) A

Output:

role:1 producer:12 timestamp:1468640222571000 latlng{latitude_e7:416989394 longitude_e7:-738966210} radius:19764

Just swap out BASE64_COL_NAME for your column name, or you can replace sql:column("BASE64_COLUMN") with sql:variable("@base64variable") if you want to use a declared variable e.g. if you are making a function or something.

It makes use of an XSL transform using built-in XML functionality (since SQL Server 2005)


Purge old records from mysql ibdata1 file Is it possible to purge old records from an ibdata1 file without taking down the database attached to it?

Records that have been deleted from the database are still present when I scan ibdata1 for strings.

I am somewhat aware of how the ibdata1 file works and realize that those portions of the file will probably be overwritten in time, but I need to zero out or otherwise remove the old data so it is no longer stored on the server in any form.

Is there anyway to do this without taking down the database?

What you are asking for has never been documented or attempted.

However, I have answered two posts like this in the past:

Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Dec 17, 2012 : Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

I have an additional suggestion for data you are going to delete, going forward. You will have to put some good developer effort in this one:

Before dropping a table, you should

go to every INT and FLOAT value and make them 0
go to every CHAR, VARCHAR and TEXT field

get the length of the field
update the column with the same number of bytes with all Xs or all 0s

You will have to examine the entry in INFORMATION_SCHEMA.COLUMNS for every column to determine which is numeric and which is character. Once you have manually overwritten all data in the old table, then you are free to drop it, knowing you  masked the data beforehand.

CAVEAT

If all your InnoDB tables have no constraints/foreign keys, here is something you can do during some down time.

Convert all InnoDB tables it to MyISAM
Shutdown mysql
delete ibdata1
Startup MySQL
Convert those MyISAM tables back to InnoDB

I can't take credit for that idea. Shlomi Noach suggested it back on September 19, 2013.

If the DB is not too big, here is how you can script it.

MYSQL_USER=root
MYSQL_PASS=password
MYSL_CONN="-u${MYSQL_CONN} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Convert_To_MyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Convert_To_InnoDB.sql
mysql ${MYSQL_CONN} -ANe"SET GLOBAL innodb_fast_shutdown = 0"

At this point, look at the two scripts in vi or less.

If you are satisfied with the code, then you can do this:

mysql ${MYSQL_CONN} < Convert_To_MyISAM.sql
service mysql stop
rm -f /var/lib/mysql/ibdata1
service mysql start
mysql ${MYSQL_CONN} < Convert_To_InnoDB.sql

Give it a Try !!!


SQL: get product stock of all product types in a single query I want to get stock information for all the products depending upon the product type.
The tables are products, product_stocks, complex1_childs & complex2_childs.
products

| product_id | type      |
|------------|-----------|
| 100        | simple    |
| 101        | simple    |
| 103        | complex_1 |
| 104        | simple    |
| 105        | complex_2 |
| 106        | complex_1 |
| 107        | simple    |
| 108        | simple    |
| 109        | simple    |

The available product types are:

simple
complex_1
complex_2

complex_* type is a compound product which includes other products as a child.

product_stocks

| product_id | qty |
|------------|-----|
| 100        | 10  |
| 101        | 50  |
| 104        | 10  |
| 107        | 25  |
| 108        | 100 |
| 109        | 40  |

As you can see only simple product type have qty info.

complex1_childs

| parent_id | child_id |
|-----------|----------|
| 103       | 101      |
| 106       | 107      |
| 106       | 101      |

Child products are always simple type.

complex2_childs

| parent_id | child_id |
|-----------|----------|
| 105       | 107      |
| 105       | 108      |

Child products are always simple type.

Now I want to get stock information for all the products as:

| product_id | final_qty |
|------------|-----------|
| 100        | 10        |
| 101        | 50        |
| 103        | 50        |
| 104        | 10        |
| 105        | 125       |
| 106        | 75        |
| 107        | 25        |
| 108        | 100       |
| 109        | 40        |

Note that qty for complex_* product types are calculated as sum of their child products' qty.

You have effectively have these 3 queries:

simple

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN product_stocks AS s
  ON p.product_id = s.product_id AND
     p.type = 'simple'
GROUP BY p.product_id

complex 1:

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN complex1_childs AS c
  ON p.product_id = c.parent_id AND
     p.type = 'complex_1'
JOIN products AS cp
  ON c.child_id = cp.product_id
JOIN product_stocks AS s
  ON cp.product_id = s.product_id
GROUP BY p.product_id

complex 2 is the same as above changing the table and the p.type join criteria.

You can UNION these together into a single query.


Slow DELETE in MySQL with a subquery I am running the following DELETE SQL on a MySQL database on a MyISAM/utf8 table.  The SQL is called in a loop, once time per Users (in this case):-

DELETE FROM regulatory WHERE documentid  IN
(
    SELECT * FROM
    (
        SELECT t1.`DocumentID` FROM regulatory T1, regulatory T2
        WHERE (t1.`group_ID`=v_user_id AND t2.`group_ID`=v_user_id AND
        TRIM(t1.`title`)=TRIM(t2.`title`) AND  TRIM(t1.`spare1`='red.gif')
        AND (t1.`docref`=v_temp) AND t1.`DocumentID`>t2.`DocumentID`)
    ) DocumentID
) ;

But it is taking over a minute per User - how can I optimize this at all so it takes far less time each user?

Try removing the nesting levels, mysql can not delete from a table that's referenced as a child, instead there's a propriety trick like:

    DELETE t1.*
    FROM regulatory T1
    JOIN regulatory T2
        ON t1.group_ID=v_user_id
       AND t2.group_ID=v_user_id
       AND TRIM(t1.title)=TRIM(t2.title)
       AND TRIM(t1.spare1='red.gif')
       AND t1.docref=v_temp
       AND t1.DocumentID>t2.DocumentID

Also, like @Mihal suggested try removing all function calls like trim from predicates, they are expensive.


Quorum disk conundrum I'm having a problem with a 2-node active-active cluster which has been failing over lately but not bringing resources back online.

We've had a Microsoft engineer look at it and, among other things, he noted that it was unusual to have a Quorum disk group explicitly created under the Services and Applications node and thought that this might cause a problem.

I'm a bit puzzled as to whether this is a factor or not. Certainly most of the documentation on quorum disk configurations don't create a specific cluster group to contain the Quorum disk - just going through the Quorum Disk Configuration Wizard seems to be all that is required.

I had always thought of a Quorum disk as being a 'fixed point' - i.e. it doesn't belong to a cluster group and doesn't fail over when a node fails over as to do so might mean the cluster no longer being in quorum (quorate?). However other documents I've read do suggest that the Quorum disk does 'belong' to a node. However if you have an active-active configuration which one gets to own it?

You should not have the quorum disk as a resource inside a "Service or Application".  It needs to be configured separately, and should be visible only under the "Disk Witness in Quorum" section in the "Storage" configuration pane.

When the cluster is configured with either "No Majority - Disk Only" or "Node and Disk Majority", the cluster service uses the quorum disk as a "vote" - since the disk can only reside at a single node.  If the nodes cannot communicate with each, only a single node will be able to connect to the disk and obtain the required number of votes to achieve quorum.  This prevents a partition where both nodes think they can run the services and applications configured in the cluster.  If no nodes can connect to the quorum disk, none of the nodes will run the cluster services and applications.

Assigning the quorum disk to a Service or Application means the disk will go offline when a failover occurs.  If the disk goes offline, it cannot be used as a vote, and the entire cluster will not operate.

See Windows Server 2008 R2 Cluster Docs and The process of achieving quorum for details


Is there a JDBC Driver for Microsoft SSAS Tabular Databases? Is there a JDBC Driver for Microsoft SSAS Tabular Databases?  Is so where can they be downloaded?

There used to be a OLAP JDBC driver that supported SSAS called olap4j but it has not been updated in a while and I am not sure of its support for anything above 2012. It is always worth a try though.

Communication with an SSAS instance is done using XMLA , which itself is based upon the SOAP protocol. Technically you should be able to send queries using a standard SOAP client with embedded XMLA statements.

That being said, I know that Microsoft uses a proprietary implementation for their SOAP over TCP/IP so you may need to tinker with your client to get it to work.

Some additional detail is given in FrankPI's answer on StackOverflow where somebody asked a similar question and there is even a suggestion open for Power BI that requests interoperability between Java and SSAS.

If none of the above solutions work for you I am afraid you will have to roll your own or use the supported ADOMD.NET components.


Error: set_valued function called in context that cannot accept a set. What is it about? I use Postgresql 9.1, with ubuntu 12.04.

Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a series generator into this plpgsql function:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns setof record as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;
end;
$$    language plpgsql;

During execution I get the error:

ERROR: set_valued function called in context that cannot accept a set

What is wrong ? Contrary to Craig I tell the function to return setof record.

I can achieve something that work doing exactly like Craig, i.e. by defining a type create type pair_id_value as (idx bigint, value integer) and have my plpgsql function returns a setof of pair_id_value instead of a setof record.

But even with this working solution, I still don't understand why select id, generate_series(0,13) alone will return a result in two columns... and on the contrary calling the function (returns setof pair_id_value) with return query select id, generate_series(0,my_obj.value) from my_obj will return a result in only one column which field look like this "(123123,0)" "(123123,1)" "(123123,2)" (3 rows)  which are tuples obviously.

Is it a case where a temporary table must/should be created ?

The error message isn't very helpful:

regress=> SELECT * FROM  compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM  compute_all_pair_by_craig(100);

but if you rephrase the query to call it as a proper set-returning function you'll see the real problem:

regress=> SELECT * FROM compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM compute_all_pair_by_craig(100);

If you're using SETOF RECORD without an OUT parameter list you must specify the results in the calling statement, eg:

regress=> SELECT * FROM compute_all_pair_by_craig(100) theresult(a integer, b integer);

However, it's much better to use RETURNS TABLE or OUT parameters. With the former syntax your function would be:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns table(a integer, b integer) as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;
end;
$$ language plpgsql;

This is callable in SELECT-list context and can be used without creating a type explicitly or specifying the result structure at the call site.

As for the second half of the question, what's happening is that the 1st case specifies two separate columns in a SELECT-list, wheras the second returns a single composite. It's actually not to do with how you're returning the result, but how you're invoking the function. If we create the sample function:

CREATE OR REPLACE FUNCTION twocols() RETURNS TABLE(a integer, b integer)
AS $$ SELECT x, x FROM generate_series(1,5) x; $$ LANGUAGE sql;

You'll see the difference in the two ways to call a set-returning function - in the SELECT list, a PostgreSQL specific non-standard extension with quirky behaviour:

regress=> SELECT twocols();
 twocols
---------
 (1,1)
 (2,2)
 (3,3)
 (4,4)
 (5,5)
(5 rows)

or as a table in the more standard way:

regress=> SELECT * FROM twocols();
 a | b
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)


PostgreSQL: pg_rewind complains about wal_log_hints = on not been set, it is I'm rather new to pg so please bear with me.

I've set up two hosts and installed pg10 on both. Then I configured log streaming replication between them. This worked fine.

I wanted to test fail over, so I stopped postgresql service on Primary and create trigger file on Standby, this also worked find. I check and my standby is not in recovery mode anymore.

Now I want to recover old Primary as new standby. I tried using pg_rewind but it gives me the following error message

/usr/lib/postgresql/10/bin/pg_rewind --target-pgdata=/var/lib/postgresql/10/main --source-server="host=server2ip port=5432 user=postgres"

target server needs to use either data checksums or "wal_log_hints = on"
Failure, exiting

I have wal_log_hints = on set on both servers.

Can anybody help me figure out how to get this to work.

Below are my settings which are the same on both servers. Replication before failing Primary worked great.

Below are lines which I changed in postgresql .conf

listen_addresses = '*'
max_connections = 200
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 5
wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = '/bin/true'
max_wal_senders = 5
wal_keep_segments = 5000
max_replication_slots = 5
effective_cache_size = 2GB

I also added the below lines to pg_hba.conf file

host    all             postgres        server1ip/24        trust
host    all             postgres        server2ip/24        trust

And finally I create recovery.conf file as below.

standby_mode          = 'on'
primary_conninfo      = 'host=sciontsdbbc01 port=5432 user=replicate password=rep-password'
trigger_file = '/tmp/PG_MasterNow'

I have wal_log_hints = on set on both servers.

But presumably it was set too late, that is, after the generation of at least some of the WAL files involved in the rewind (in the pg_wal directory).


Update files or a table every 5 seconds or for every update to a table whichever is the less frequent There is a table 'votescount' which is being updated every time a user vote.

I need to display three rows with maximum votes.

Now the problem is If the users are less in number and less frequent to update, a trigger will be better to extract the rows, otherwise, if users are more frequently voting, an event will be better.

So, is there a lightweight solution in between to solve the problem?

I'll prefer to write the results in a file because it will be faster to access from php.

I am working on a LAMP stack.

This worked for me.

create event event_name on schedule every 5 second starts now()
do
begin
if (select update_time+5>=now() from information_schema.TABLES
where table_name='Table_name')
then call procedure_name();
end if;
end//

Here the event checks every 5 seconds if any update happened in the last 5 seconds on the table. If yes it simply calls the procedure update_file or something.


Can I use GIT directly within Oracle I'm familiar with Git, I use it every day for web projects. But I'm trying to use it with an Oracle DB, mainly to track different procedures and packages.

This Oracle DB lives on a different server, not my local machine, and I am not the DBA.

Is it still possible for me to configure a solution to track this information, and if so, how? I wouldn't have the first clue as to what folder I would initialize as the repo.

Really what you're asking for is how can I deploy code/schema/data to a Oracle DB in a remote server, from a Git repo.  This can also be known as orchestration if done as part of a bigger process, or automated code deployment which leads to Continuous Integration/Deployment.

There are there are tons of solutions from your own homebrew options such as scripting it out.  The more commonly used tools for this are Jenkins, and you use .Net, Octopus is great.  Easiest solution is to write a Python/Powershell/whatever script to pull down the latest build, validate it, and execute it as statements then evaluate the return.

These tools work similarly together.  They often provide a web UI for you to communicate with multiple machines across domains and trusts with agents and users executing remotely or directly on the machine as the user.

For example, Octopus which works for .Net, deploys an agent known as a tentacle on the machine that all actions are executed under and under it's user on the remote machines (Oracle DB Server).  You can then have actions such as 'git pull' directly to your prod ops folder, then the next step verifies it and passes in variables directly from Octopus and continues executing statements.

Moving fwd, you could use these  as a central part of moving to the SDLC best practices involving C.I.   Jenkins could be used in similar methods for other software.  It is used extensively in management software such as Cloudera Manager to manage big Hadoop clusters.


Unable to edit some records in SQL I have a table we just transferred from MS Access to SQL Server 2016.

It's a simple table for creating load numbers for shipping products. Using access we link to the SQL Server and we are able to add new records and create a load number but we can not edit the record after that. What setting am I missing?

It must be in permissions. Any ideas?  I have noticed that I can edit some older records and records that I create so it must be a permissions setting somewhere.

The "Write Conflict" dialog is discussed in the Microsoft documentation for MS Access here:

Write Conflict dialog box

  Microsoft Access displays the Write Conflict dialog box when you and another person are attempting to write to the same record in the database at the same time.

That seems pretty straightforward - if other people have access to the SQL Server table (likely), then they have possibly changed the row you're trying to change since the last time results were loaded for you.  One way to work around this problem is to periodically refresh your view of the table (for example, after each edit you make).

In practice, there are some caveats to this process with SQL Server specifically around NULL values in the data rows, as well as the presence of a Primary Key and / or Timestamp definition for the rows.  These are discussed in this Stack Overflow post:

Editing Record issues in Access / SQL (Write Conflict)


Introduce partitions into an existing non-partitioned table What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this?

Especially in cases where there are many constraints on the table like foreign keys.

I think this document will demonstrate the steps needed. IT is not a non-trivial thing to do.


InnoDB buffer. Should I increase buffer size? Database monitor

InnoDB and Key always 100%. How can I increase buffer size ?
can this cause any problems or slow database speed?

My configuration

skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 32M
table_open_cache = 256
sort_buffer_size = 4M
read_buffer_size = M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size= 64M
thread_concurrency = 16

#innodb_use_native_aio = 0
innodb_file_per_table

Short answer:  Set innodb_buffer_pool_size to 70% of available RAM (assuming you have more than 4GB of RAM).

Long answer:  For deeper investigation, we would need SHOW VARIABLES, SHOW GLOBAL STATUS and the RAM size.  And the buffer being "full" does not mean that things are running poorly -- it is a cache.


Query plan missing ParameterCompiledValue Recently I've started to analyze execution plans. Today I stumbled on an execution plan that has no ParameterCompiledValue (and no ParameterList tag).

It is a plan of a select with one inner join and seven left outer joins.

Is there a reason why some execution plans have a ParameterCompiledValue and others don't?

Edit:

Is it correct to stay the ParameterList is empty due to the fact that sql server could not sniff the parameters? It is not an ad-hoc query and is exectued by a third party c# application.
The query and queryplan can be found on Google drive

The XML plan uploaded includes:

<ParameterList>
    <ColumnReference Column="@today" ParameterCompiledValue="'2016-03-04 00:00:00.000'" />
</ParameterList>

There are multiple statements in the batch, so the XML contains multiple <StmtSimple> elements under <Statements>. Aside from the final select, the other statements are all assignments, which are not queries, so there is no parameter list at that level.

As Kin mentioned, you may find it useful to look at the plans in SQL Sentry Plan Explorer:

Related question:

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN


What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns? What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns?

My manager said this is well known practice and query performance is good. Is this true?

Thanks in advance

Edit: and this columns are put under a clustered index.

Edit: This is not a accounting database. There is no need for thirteen month or 30 Feb.

Edit: table script

/****** Object:  Table [dbo].[tbl_PlayerActionLog]    Script Date: 14/8/2014 9:47:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_PlayerActionLog](
    [LogId] [bigint] IDENTITY(1,1) NOT NULL,
    [LogYear] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogYear]  DEFAULT (datepart(year,sysdatetime())),
    [LogMonth] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogMonth]  DEFAULT (datepart(month,sysdatetime())),
    [LogDay] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogDay]  DEFAULT (datepart(day,sysdatetime())),
    [LogTime] [time](7) NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogTime]  DEFAULT (sysdatetime()),
    [PlayerActionTypeId] [int] NOT NULL,
    [MerchantId] [int] NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [BetModeId] [int] NULL,
    [OSVersion] [varchar](20) NULL,
    [SessionId] [varchar](30) NULL,
    [IpAddress] [varchar](15) NOT NULL,
    [LogDetail] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_tbl_PlayerActionLog] PRIMARY KEY NONCLUSTERED
(
    [LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_PlayerActionLog]  WITH CHECK ADD  CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType] FOREIGN KEY([PlayerActionTypeId])
REFERENCES [dbo].[tbl_LookupPlayerActionType] ([PlayerActionTypeId])
GO

ALTER TABLE [dbo].[tbl_PlayerActionLog] CHECK CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType]
GO
/****** Object:  Index [tbl_PlayerActionLog_idx]    Script Date: 14/8/2014 10:00:23 AM ******/
CREATE CLUSTERED INDEX [tbl_PlayerActionLog_idx] ON [dbo].[tbl_PlayerActionLog]
(
    [LogYear] ASC,
    [LogMonth] ASC,
    [LogDay] ASC,
    [LogTime] ASC,
    [MerchantId] ASC,
    [PlayerActionTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I guess it depends on how you want to use that data. There may be some advantage to the application using it. For example it might make reporting or filtering by year or month easier.

But I've never heard of that as some kind of best practice on its own.

The downside of this approach is that you lose the ability to use the standard date functions in SQL. Simple things like duration between 2 dates or times is simple using datetime fields but a nightmare with ints. You also lose the inherent validation of a proper date datatype. Ask your  boss if there is anything stopping you from inserting 30th of February or a 13th month.


Influence of index rebuild tasks in tempdb on the transaction log? I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before...

The goal is to replace some simple rebuild maintenance plans with a more flexible and fragmentation driven solution to follow best practice and perform required actions instead of not required actions. So following suggestions like this by Kendra Little and a lot of other smart people, I headed for Ola Hallengreen's solution.

So far everything is setup properly, following Brent Ozar's suggestion to create two different jobs (one less-invasive e.g. daily running reorg / online rebuild job and one weekend job that does more and maybe offline processing) and ready to go.

But I am unsure and little concerned about the (side) effects.

Because we have couple of very large tables in place I tried to find out the effects of index rebuilds on transaction logs watching the log file size and number of entris growth based on the following code that I found in this question:

backup log myDB to disk = 'NUL'
-- check log space/usage:
dbcc sqlperf (logspace)
-- query index log records
SELECT LogRecordsGenerated = COUNT(*)  FROM sys.fn_dblog(NULL, NULL)  WHERE AllocUnitName = 'dbo.TableName.indexName'
-- COMMINT THE INDEX OPERATION (Online / Offline)
-- Check the above values again...

It is suprinsing that index rebuild OFFLINE tasks require log space and produce transaction log entries while ONLINE does not! I expected it the other way round...?

The questions I can't find answers for are:

How will the rebuild online or offline jobs affect Log File Growth? How can I measure this? Is there a better way?
How much space do I have to reserve in my data files for the rebuild process? I found hints for around 110%-120% of the indexes size (e.g. in this MS document). In case all my indexes together use about 10 GB, does it mean additional size of 12 GB has to be available in the data file? Or wil lthe index rebuild job that sequentially maintains one index after another re-use the available space and release it after finished an index?
In case I use SORT_IN_TEMP, will the transaction log growth happen in the tempdb as well? Also if it is set to SIMPLE recovery mode?

Both offline and online reorg/rebuild operations consume log space.  This is true for ALL operations that modify a database in any way, even for minimally logged operations such as TRUNCATE TABLE.  This is how SQL Server maintains transactional consistency.  "Consistency" is impossible unless every action is logged so it can be rolled back or forward if necessary.

Index reorganization uses less log space than an index rebuild.  Reorganization defragments the index page-by-page, whereas a rebuild creates an entirely new copy of the index being rebuilt, then drops the old copy of the index.  If an individual index is 1GB, rebuilding it will require at least 1GB, plus space for any rollback to take place that may happen as a result of the rebuild either failing or being cancelled.  Since any rollback operation will also be logged, log space is reserved prior to the start of all transactions to ensure the transaction can be rolled back successfully.  This indicates the maximum potential log space required for an index rebuild would be double the size of the index at the time the rebuild operation begins.  So for a 1GB index, you should ensure you can support at least 2GB of log space in use by the rebuild operation for the duration of the operation.  This is not a guarantee that much space will be used, it is simply the maximum the operation might need.  Any other simultaneous transactions occurring will need additional space.

Any operations that consume tempdb also consume tempdb log space.  If you enable SORT_IN_TEMPDB, you need enough room in tempdb log to support sorting the entire index; this amount depends greatly on the structure of the index, including things like how large the key columns are.

If index reorg/rebuild operations are performed serially, that is one-after-another, then you'll need space in the transaction log for the largest index size x 2.  i.e. if your largest index is 1GB, to be safe you need to ensure 2GB of transaction log is available for your index operation.  If you have simple logging in effect at the time of the reorg/rebuild job, and all operations aren't in a single transaction, transaction log space will be re-used by each individual operation.  If you have full transaction logging enabled, and no transaction log backups occur during the reorg/rebuild job, then the log needs to be size at the total size of all indexes to be rebuilt + the size of the largest index to be rebuilt.  This can be mitigated somewhat by running log backup jobs during the rebuild process.


How to simplify this query? I am somewhat new to Access and I need help with a query that Access does not execute because of Not enough space on temporary disk.

I have one table. A product may have up to 3 IDs and a place to sell. If there is a place to sell, the name will be shown along with a combined identifier consisting of the three IDs plus the "k1" prefix. An example of the resulting combined identifier would be "k1 3232 232 1231233" (if it has three IDs).

The column names are ProductID1, ProductID2, ProductID3, Place1.

This is the query:

SELECT DISTINCT IIf(IsNull([Tam_Liste]![Place1]),"","k1 " & [Tam_Liste]![ProductID1] & " " & [Tam_Liste]![ProductID2] & " " & [Tam_Liste]![ProductID3]) AS item_sku, Tam_Liste.[Place1]
FROM Tam_Liste;

For every row where Place1 is null, you are returning an empty string as item_sku. So without DISTINCT all such entries would be returned as

item_sku  Place1
--------  ------
          NULL
          NULL
          NULL
          NULL
          NULL
…         …

and DISTINCT condenses them to just one row. Do you need that row in the output? To me, it does not make much sense to keep it. I would get rid of it with a simple filter:

SELECT DISTINCT
  "k1 " & ProductID1 & " " & ProductID2 & " " & ProductID3 AS item_sku,
  Place1
FROM
  Tam_Liste
WHERE
  Place1 IS NOT NULL
;

Furthermore, I would also consider moving the concatenation bit to the front end and use the database only as a data source*. My final query, therefore, would look like this:

SELECT DISTINCT
  ProductID1,
  ProductID2,
  ProductID3,
  Place1
FROM
  Tam_Liste
WHERE
  Place1 IS NOT NULL
;

I am not 100% sure but it seems likely to me that the above query would require less temporary disk space than the original one.

*I realise that things may work differently with Access, as it can be used both as a back end and as a front end. Nevertheless, I would still keep this option in mind.